EXEC nested Stored Procedures - 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 2005 Forums

Transact-SQL (2005)

EXEC nested Stored Procedures

Author

Topic

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-03-21 : 14:32:32

I am working on a project where I need to call several stored prodecures and manipulate the data returned.

I know, why not manipulate the data in the SP and save a ton of time, but I am not able to change the SP's. They are very large and they do not want them duplicated.

My first attempt was to
Create a #TempTable
and Then
Insert #TempTable Exec StoredProcedure

The problem with the above was that the SP's are nested and this won't work for them.

I even tried OPENQuery but performance seems to be an issue.

I am not able to Create a CLR or UDF to facilitate this issue.
Does anyone out there have any ideas about how I could call these Nested SP's and play with the data returned?

Any help would be greatly appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-21 : 15:11:05

I don't understand why insert #... EXEC sproc won't work for you. Just because the stored procedures are nested doesn't mean you can't put the result set into a temp table. Could you show us what the output of the stored procedure looks like?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-03-21 : 15:27:25

Here is the exact line I use to call the SProc...
Insert #Temp_Table1 Exec ComADLCalc 4,@Select
4 = variable
@select= select statement that the SProc will manipulate to a point.

here is the error I get.
Msg 8164, Level 16, State 1, Procedure ComADLCalc, Line 104
An INSERT EXEC statement cannot be nested.

When I researched it online, Microsoft doesn't allow you to use the insert exec statement on a nested stored procedure.
My stored procedures have several nested queries and calls in them.

I can show you the output of the stored procedure, but it is very large. it has many columns to it. So I didn't want to put all that there and everyone stop reading this post.
but it looks like a view of many tables and calculations put in.

Thanks for any help.
If you still need to see what it returns, I will put it on here.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-21 : 15:35:56

I don't understand what you are doing with @Select. Is the stored procedure using dynamic SQL? Will the returned output be dynamic, meaning the columns?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-03-21 : 15:45:47

@Select = Select * from TAble1 left join table2 on Table2.id = table1.id ... so on

The @select is a dynamic select statement that is passed to the Stored procedure as a string and then the SP runs it and then manipulates the data into a standard output with Codes in them that the SP created.

So the output is always the same Columns, but there are code columns that are there we need.

Now what I have to do is look at the code columns and come up with a new code for reporting. It was requested that I use the old SP's for this.

So in other words i need to call this stored procedure and put the standard returned data into a temp table and come up with new codes based on the original codes. The other issue is I need to call several SP's that return standard outputs with Code Columns to come up with the new codes.

I hope I didn't confuse you more.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-21 : 15:54:16

I am able to insert #table exec sproc when the sproc uses nested stored procedures.

Here is my example:


SELECT *
INTO #Temp
FROM sysobjects
WHERE 1 = 0
GO

CREATE PROC sp1 AS SELECT * FROM sysobjects
GO

CREATE PROC sp2 AS EXEC sp1
GO

CREATE PROC sp3 AS EXEC sp2
GO

INSERT #Temp
EXEC sp3

SELECT * FROM #Temp

DROP TABLE #Temp

DROP PROC sp1, sp2, sp3

Could you post a link to the Microsoft article that you found.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-03-21 : 16:07:48

this is microsofts one, but a good one.
http://www.sommarskog.se/share_data.html

I will try to find the Microsoft one and post it.

You have proven that it works, but I still can not get it to work???? I will keep researching this... I see a ton of people on different forums having the same problem with out any answers that work. Maybe it is the type of nesting or something that is being done????

Thank you for your help thus far.

morphviper
Yak Posting Veteran

60 Posts

Posted - 2007-03-21 : 16:51:21

I figured out why it is erroring out!

I have insert exec statements in the stored procedure!
this causes the Nested error that I am getting.

Do you or anyone have a work around on this that doesn't require OpenQuery or changing the Stored Procedure?

thank you for any suggestions or help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources