6

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.

  • 1
    you want to insert these values in one column or 4 columns? – Learning_DBAdmin Apr 23 at 12:19
  • I want to insert the values in 1 column – Pantea Tourang Apr 23 at 14:14
  • 1
    Just so you know, in general, looping isn't very "sql"-esque. That doesn't mean it's necessarily bad, but it's a strong yellow flag that you may be using SQL more like a traditional programming language rather than as a query language. – David Rice Apr 23 at 20:10
11

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.