Zrjydtj

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
ect
End

SET @I +=1
end