Mdthbs

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.

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

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

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);

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