Help with INSERT INTO table from Stored PRoc

Question

Help with INSERT INTO table from Stored PRoc

Danster

SSCommitted

Points: 1632
More actions
July 18, 2006 at 9:38 pm

#91663

Hi all,
We all know that the results of a stored procedure can be added to a table with the statement
INSERT INTO #Table EXEC SPName
I have a problem where I need to pass the SP some variables so I go
INSERT INTO #Table EXEC SPName @var1, @Var2 which works fine, I get the results I need into #Table BUT it gets tricky when I also need the variable values on the SAME ROW in #Table.
I don't have the luxury of being able to edit the stored proc so I need those values to come from the INSERT INTO.
Any ideas?
Thx in advance
Danster

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Danster

Assuming your temp table contains columns col1, col2 and col3 for the SP results and var1 and var2 for the variables, try this:

INSERT INTO #Table (col1, col2 col3) EXEC SPName

UPDATE #Table SET var1 = @var1, var2 = @var2

SELECT * FROM #Table

If you are running this more than once during the life of the temp table then you will have to do something a bit more complicated, such as putting an identity column in the table and using this to ensure that you only update the rows you have just inserted.

John

bledu SSChampion Points: 12335 More actions · Answer 2

yap, i think john's suggestion should work for you

Everything you can imagine is real.

Tia Salau Newbie Points: 9 More actions · Answer 3

I agree with John's idea. It should work for you all the time. If you are going to run the proc more than once within the life of the temptable, you should run the update immediately after the proc is run each time. You can modify the update like this:

UPDATE #Table SET var1 = @var1, var2 = @var2

WHERE var1 is null and var2 is null

Good uck

Danster SSCommitted Points: 1632 More actions · Answer 4

Thanks for the replies. Looking at all answers given (I have a similar post elsewhere) it appears that I can't include the variables in the same INSERT INTO, meaning I need the following UPDATE as John suggested.

John's suggestion would work but as he mentioned I'll need an identity col coz I run the INSERT INTO multiple times so I need to update just the inserted row.

I can easily add an indentity column to the temp table no problems. Would the following syntax work?

INSERT INTO #Table (col1, col2 col3) EXEC SPName

UPDATE #Table SET var1 = @var1, var2 = @var2 WHERE IdentityCol = (SELECT Top 1 IdentityCol From #Table ORDER BY IdentityCol DESC)

SELECT * FROM #Table

or maybe

INSERT INTO #Table (col1, col2 col3) EXEC SPName

UPDATE #Table SET var1 = @var1, var2 = @var2 WHERE IdentityCol = (SELECT Max(IdentityCol) FROM #Table)

SELECT * FROM #Table

Other ideas?

Thanks all

Danster

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Danster

That would work, so long as your stored procedure's result set consists of only one row. I think my original idea for making sure you only update what you've just inserted was a little too elaborate - Tia's would work just as well. However, you need to wrap it all in a transaction just in case you have two processes trying to do the same thing at the same time.

John