October 14, 2009 at 8:36 am
I a complicated count stored procedure (sp2) that needs to get run within my main stored procedure (sp1). I want to get that count value into a variable to then be inserted into a table.
added info:
Sp2 has a parameter passed into it.
I've been able to skip the storing the value in the variable part and just insert the value into a table, however my problem is that I need to actually run 3 stored procedures and get those values along with data stored in variables in the main stored procedure and then insert all 5 values into a table.
Any ideas?
October 14, 2009 at 9:40 am
An output parameter from Sp2 would be available within sp1. Have you tried that?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 14, 2009 at 9:41 am
in order to get the three values from the stored procedures into a global table, they seem to have to be three distinct insert statements, which causes there to be three rows in the table instead of 1, though I'm sure I could be missing something about global temp tables
October 14, 2009 at 9:58 am
Actually Grant's idea is better; I haven't done that type of thing for so long I'd forgotten about it.
But just because a global temporary table is accessed from three procedures doesn't mean you have to have three rows within it. For these purposes it works just like any other table - you can insert from one SP and update an existing row from the others. You can use identity columns if required - no difference from a permanent table.
BrainDonor.
October 14, 2009 at 10:05 am
In 2008 you could make a table variable and pass that between stored procedures, but you'd probably still need to make it an output parameter to get it back out of a second procedure. Not sure. I haven't tested that much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 14, 2009 at 10:32 am
does the output parameter need to be declared in each of the sub procedures then?
October 14, 2009 at 10:35 am
Yeah. This is untested psuedo-code, but it illustrates what you do:
CREATE PROCEDURE A
(@p1 int)
AS
EXEC B @p2 = @p1 OUTPUT;
SELECT @p1;
GO
CREATE PROCEDURE B
(@p2 int OUTPUT)
SET @p2 = 42;
GO
You can look up OUTPUT parameters in the books online for details, but I think it'll do what you want based on what you've said.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 14, 2009 at 11:02 am
Thats not gonna work unfortunately, b/c I can't change any of the three called stored procedures, the developers need them as is.
I tried inserting the sp2 into colA of a temp table and that works fine, but as I said before, when I go to run sp3 and sp4 to go into colB and colC respectively, they go on separate rows.
I then tried
update #temp
set colB = exec sp3 inputpar, colC = exec sp4 inputpar
where id = @id
but I can't get that to work nor have I found anything remotely helpful through google yet
October 14, 2009 at 11:17 am
Sorry. It sounds like to do what you want, you need to make a change to the code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 14, 2009 at 12:05 pm
I was afraid of that. Thanks for the quick replys
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply