Nested stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Nested stored procedure

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-23 : 06:19:33

Hello all,
in my SQL Server 2008 database I have a stored procedure that populate a temporary table #T1.
At the end I have an Exec statement to another stored procedure:

INSERT INTO #T1
EXEC Report_AddingC926Bis @ImpiantiID, @DataDa, @DataA

and in the Report_AddingC926Bis sproc I have several other Exec statement (to other stored procedure).

If I run Report_AddingC926Bis alone, everything works fine, but if I run the sproc container I got this error:

An INSERT EXEC statement cannot be nested.

How can I solve this problem?

Thanks in advance.

Luigi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-23 : 06:49:09

This is a limitation of the INSERT-EXEC method of passing data between stored procedures. There are other ways - take a look at Sommerskog's article here: http://www.sommarskog.se/share_data.html

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-23 : 08:15:09

I was trying to rewrite my second stored procedure like a table value function, but it gives me this error:

Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

I think because in this function there are several EXEC sprocs statements.

Luigi

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-23 : 08:30:26

That may be because of the limitations of UDFs rather than multiple execs. The limitation is that a UDF cannot be allowed to cause side effects that affect the database state outside the scope of the function. Also UDFs cannot create or access temporary tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 10:58:37

quote:
Originally posted by Ciupaz

Hello all,
in my SQL Server 2008 database I have a stored procedure that populate a temporary table #T1.
At the end I have an Exec statement to another stored procedure:

INSERT INTO #T1
EXEC Report_AddingC926Bis @ImpiantiID, @DataDa, @DataA

and in the Report_AddingC926Bis sproc I have several other Exec statement (to other stored procedure).

If I run Report_AddingC926Bis alone, everything works fine, but if I run the sproc container I got this error:

An INSERT EXEC statement cannot be nested.

How can I solve this problem?

Thanks in advance.

Luigi

out of cusriosity whats it that you're trying to achieve using nested proc calls? why cant it be handled inside same proc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-23 : 14:21:36

Because every sproc is very long and complicated. Essentially for this reason.

Luigi

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-23 : 14:26:28

Is it a good idea to create a table (psysically), that I populate with the second sprocs and then I read inside the first sproc?

L

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-23 : 14:32:06

With physical table you can achieve, what you want, but ITS NOT CONSIDERED A GOOD IDEA. How you will manage it with multi-users calls. Visakh, idea is much better to bring second stored procedure logic to a single stored procedure.

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 15:07:02

quote:
Originally posted by Ciupaz

Because every sproc is very long and complicated. Essentially for this reason.

Luigi

can you give us a brief overview of an example of whats happening inside a proc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ciupaz
Posting Yak Master

232 Posts

Posted - 2012-07-24 : 03:48:35

Here is my situation:

CREATEPROCEDURE [dbo].[Report_Principal] (@ImpiantiID int, @DataDa datetime, @DataA datetime)
AS

-- Table output for the report
CREATE TABLE #T1 (Indicator varchar(50), DescriptionGroup varchar(70), UM varchar(10), Value decimal(18,3), sortOrder char, NumDecRpt int, CodeGroup varchar(50))

-- Several of these statement
INSERT INTO #T1
SELECT .....FROM

...
...

-- This cause the error of EXEC
--INSERT INTO #T1
--EXEC [dbo].[Report_GetNewMeasures] @ImpiantiID, @DataDa, @DataA

SELECT * FROM @T1
ORDER BY sortOrder, Codice_Gruppo

In the second stored, I have:

CREATEPROCEDURE [dbo].[Report_GetNewMeasures] (@ImpiantiID int, @DataDa datetime, @DataA datetime)
AS

-- Table output for the report
CREATE TABLE #T1 (Indicator varchar(50), DescriptionGroup varchar(70), UM varchar(10), Value decimal(18,3), sortOrder char, NumDecRpt int, CodeGroup varchar(50))

-- Several of these statement
INSERT INTO #T1
EXEC dbo.Report_GetDataN1 @ImpiantiID, @DataDa, @DataA

INSERT INTO #T1
EXEC dbo.Report_GetDataN2 @ImpiantiID, @DataDa, @DataA

...
...

SELECT * FROM @T1
ORDER BY sortOrder, Codice_Gruppo

Here we are.
My new adding is the sproc dbo.Report_GetNewMeasures.

Luigi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-24 : 09:54:19

i cant see anything complicated in it. i would have wrapped this in single proc
Alternatively if you've to call them like this i would have replaced other nested procs with UDFs

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources