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

Question

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.

Answer 1

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

Answer 2

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

Create table #Temp(Num int)

--1st way
INSERT #Temp(Num)
SELECT @1
UNION ALL
SELECT @2
UNION ALL
SELECT @3
UNION ALL
SELECT @4


SELECT * FROM #Temp

TRUNCATE TABLE #Temp

--2nd way    
INSERT #Temp(Num)
VALUES
    (@1),
    (@2),
    (@3),
    (@4)

SELECT * FROM #Temp

DROP TABLE #Temp

Answer 3

CREATE PROCEDURE dbo.InsertFourValues (@I1 int, @I2 int, @I3 int, @I4 int)
AS

BEGIN
  --the table below must already exist
  INSERT INTO dbo.MyTable (MyIntColumn)
  VALUES (@I1), (@I2), (@I3), (@I4);

END

Now you just call it using your values:

EXEC dbo.InsertFourValues (10, 20, 30, 40);

Answer 4

The best bet is to do it with a static Insert Statement if you really want to do it via a loop you could use the value of @I to determine which value to pass into the insert statement using a case statement would be the best route for that

I.E.

While (@I <= 4)
   Begin
         Insert Into #Temp
         Select case @I when 1 then @1 
            when 2 then @2
            -- etc
            End     
     SET @I +=1
   end