HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Writing a T-SQL stored procedure to receive 4 numbers and insert them into a table

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedinsertsqlintonumberswritingprocedurereceiveandthem

Problem

I need to write a stored procedure to receive 4 numbers and insert them into a table. This is what I've developed so far:

Declare   
@1  Int = 10,
@2  Int = 20,
@3  Int = 30,
@4  Int = 40

Create table #Temp(Num int)
Declare @I char(1) = 1

 While (@I <= 4)
   Begin

         Insert Into #Temp
         Select @I

     SET @I +=1
   end

Select * from #Temp
Drop table #Temp


I know that I can directly and statically insert the inputs into the table but I just want to know is there any better way to do that? I wanted to use a while statement but the problem is the numbers for (I) variable are being inserted into the table!! I mean the output is 1,2,3,4; what I want is 10,20,30,40.

Solution

You could also use a table valued parameter type in the stored procedure and pass numbers through this tvp.

Create the type

CREATE TYPE GetNumbers AS TABLE   
( Numbers INT );  
GO


Create the procedure

CREATE PROCEDURE dbo.InsertNumbers  
@GetNumbers GetNumbers READONLY  
AS   
SET NOCOUNT ON;

CREATE TABLE #Temp(Num int);
INSERT INTO  #Temp(Num)
SELECT Numbers
FROM  @GetNumbers;  
SELECT * FROM #Temp;
DROP TABLE #Temp;
GO


Inserting into temp table is not really needed here, only done to keep it the same as the question.

Fill up a variable with data and call the procedure

/* Declare a variable that references the type. */  
DECLARE @GetNumbers AS GetNumbers;  

/* Add data to the table variable. */  
INSERT INTO @GetNumbers (Numbers)  
VALUES(10),(20),(30),(40);

/* Pass the table variable data to a stored procedure. */  
EXEC InsertNumbers @GetNumbers;


The example used and more on tvp's here

Code Snippets

CREATE TYPE GetNumbers AS TABLE   
( Numbers INT );  
GO
CREATE PROCEDURE dbo.InsertNumbers  
@GetNumbers GetNumbers READONLY  
AS   
SET NOCOUNT ON;

CREATE TABLE #Temp(Num int);
INSERT INTO  #Temp(Num)
SELECT Numbers
FROM  @GetNumbers;  
SELECT * FROM #Temp;
DROP TABLE #Temp;
GO
/* Declare a variable that references the type. */  
DECLARE @GetNumbers AS GetNumbers;  

/* Add data to the table variable. */  
INSERT INTO @GetNumbers (Numbers)  
VALUES(10),(20),(30),(40);

/* Pass the table variable data to a stored procedure. */  
EXEC InsertNumbers @GetNumbers;

Context

StackExchange Database Administrators Q#235465, answer score: 11

Revisions (0)

No revisions yet.