October 29, 2009 at 11:28 pm
Hi All,
While trying to develop an online reporting panel I'm stuck on a problem I'm unable to find an answer on my own.
Hence, the request for you all to help me.
Problem:
One of the reports require me to store output of an existing SP and make some calculations on it.
Just to make it easy to understand I'm using some naming here so that I can explain it easily.
[font="Arial Black"]
Top Level (Problem) SP-> SP3
Nested SP-> SP2
Another nested SP within SP2-> SP1
[/font]
When I try to run SP3 I get this error message "An INSERT EXEC statement cannot be nested."
Reason: As much as I could debug this issue is coming in SP2 at a statement where SP2 calls and stores the output of SP1 in a temp table.
While SP2 runs smoothly on a standalone basis when its output is called in SP3 I get an error.
If this particular SP2 statement is commented and values hard-coded i.e. SP1 is never called, SP3 also runs fine.
So as the error also says it has something to do with how much deep nested SPs can work?
Further, I've tried using TEMP Table, Temporary table variable and defined a proper table to store these nested SPs output thinking just in case TEMP tables have this limitation but nothing worked.
Kindly, suggest me how do I get SP3 working with just 2 level deep nested SPs being used.
Thanks
Ankit Mathur
October 30, 2009 at 12:31 am
Hi
You cannot use nested statements like "INSERT INTO MyTable EXEC myProc", as the error says.
AFAIK there is no way to cheat this behavior. Probably you can transform one or more of your procedures to table-valued functions.
Greets
Flo
October 30, 2009 at 12:51 am
If it can work 1 level deep why not 2 level. Not even by any workaround????
I was looking for some innovative work around if possible.
But as you suggested I think I'll have to give a proper thought to table-valued functions too. I wasn't in too much favour of a function coz my SPs are doing some heavy computing on a large set of records (I'm not sure how well computing in function would respond for a record set which is in millions) and I need to use print statements for debugging which won't be possible in functions.
One more thought, can I call my nested SP in a VIEW? I've never tried it and wonder if its possible. If anybody has tried it please let me know. I think I'd be requiring some helping hand on this too.
Thanks for the reply.
October 30, 2009 at 1:41 am
Ankit Mathur-481681 (10/30/2009)
If it can work 1 level deep why not 2 level. Not even by any workaround????
Because 1 level is not nested and two is?
Do the procs always get called like this or is there some place where just the inner one is called?
Can you post the code (or demo code with the same problem)?
One more thought, can I call my nested SP in a VIEW? I've never tried it and wonder if its possible.
No. A view must be a single select statement. An EXEC is not allowed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2009 at 2:11 am
You could try this. As suggested already converting the stored procedure to a tvf (table valued function) would be a 'better' option.
October 30, 2009 at 2:21 am
Though table value functions (well, multistatement ones) do have some performance issues.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2009 at 2:51 am
Ankit Mathur-481681 (10/30/2009)
But as you suggested I think I'll have to give a proper thought to table-valued functions too. I wasn't in too much favour of a function coz my SPs are doing some heavy computing on a large set of records (I'm not sure how well computing in function would respond for a record set which is in millions) and I need to use print statements for debugging which won't be possible in functions.
There is no significant difference in execution speed of a function and a proc,10,000 statements will execute at pretty much the same speed in both. There is an overhead in calling a func though as i think Gail meant.
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx
So if you were iteratively calling the func that the proc the yes, you would be correct. But if that were the case then you would need to use correct set based logic to remove the loop/cursor doing that anyway.
'Heavy Computing' is a very loose term. Analysing millions of rows is something SQL is best at, heavy CPU intensive work is not.
October 30, 2009 at 2:55 am
Do the procs always get called like this or is there some place where just the inner one is called?
Can you post the code (or demo code with the same problem)?
Actually in other parts of application SP2 and SP3 are being used independently. Since in this particular code I could reuse the existing SPs with little modifications I did so but got stuck on this problem.
As for the code. I won't be able to post the original code and creating a demo script would take some time but I'll try & create a script that should help recreate the problem.
In the meanwhile I'll try my hands on TVF too and let you all know about my progress on that front.
Thanks
October 30, 2009 at 3:17 am
Not quite what I meant
http://sqlinthewild.co.za/index.php/2008/08/12/views-or-functions/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2009 at 4:43 am
Ankit Mathur-481681 (10/30/2009)
I was looking for some innovative work around if possible.
I'm sure all will become clear when you post your demo code and further details, but in the meantime, here are some approaches to passing sets around (I'll leave you to decide how innovative they are):
1. Store the rows in a temporary table
create proc #p1 as begin create table #t (a int); exec #p2; select * from #t end;
go
create proc #p2 as begin insert #t values (@@IDLE) end;
go
exec #p1;
go
drop proc #p1, #p2
2. Use an output cursor variable
3. Write the CPU-intensive routine in a .NET language and add it to SQL Server as a CLR stored procedure or function. Access the input data using the context connection.
4. Upgrade to 2008 and use table-valued parameters
5. Use CLR UDTs to encapsulate data objects or arrays.
...and so on. So much depends on your specific requirements.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply