patternsqlModerate
Writing a T-SQL stored procedure to receive 4 numbers and insert them into a table
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:
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
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 #TempI 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 the procedure
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
The example used and more on tvp's here
Create the type
CREATE TYPE GetNumbers AS TABLE
( Numbers INT );
GOCreate 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;
GOInserting 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 );
GOCREATE 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.