July 18, 2006 at 9:38 pm
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
July 19, 2006 at 2:16 am
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
July 19, 2006 at 8:52 am
July 19, 2006 at 10:24 am
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
July 19, 2006 at 6:55 pm
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
July 20, 2006 at 12:15 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply