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 2012 Forums

Transact-SQL (2012)

Nested stored procedures

Author

Topic

matteo
Starting Member

2 Posts

Posted - 2015-03-27 : 07:17:42

Hi all.
I'm new on this forum and on Tsql as well. So please be patient with me if it seems to you a stupid question...
I'm trying to migrate an Access database, quite complex, to SQL Server 2012 environment.
The problem is that I can't nest the Stored Procedures since at the second level it appears the message "The instruction INSEERT EXEC can't be nested".
At the beginning I tryid the way

DECLARE @temp as table  (indice nvarchar(255), data date, rate float)
INSERT INTO @temp EXECUTE SPForexrate_valuedate @value_date

then

CREATE TABLE @temp (indice nvarchar(255), data date, rate float)
INSERT INTO @temp EXECUTE SPForexrate_valuedate @value_date

which of course doesn't work because it not resolve the use of INSERT EXECUTE call.

At the end I tried to write a function instead of the SP (as suggested into the article "How to share Data between Stored Procedures" by Erland Sommarsskog) but it appear the message "Use of an operator with collater effect 'INSERT EXEC' not allowed into a function".

How can I resolve this issue???? It seems to me impossible that there is this problem for a matter which is foundamental for big projects.
Thanks to all.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-03-27 : 08:36:25

I Have gotten away with using a global temporary table in the past for rare occasions when I needed something like this. something like this might work for you. You could also do this with a permanent table

CREATE PROC #Level1
AS
BEGIN

IF OBJECT_ID('tempdb.dbo.##T') IS NOT NULL BEGIN DROP TABLE ##T END

CREATE TABLE ##T (ID INT IDENTITY(1,1), PROCInsert varchar(25))

INSERT INTO ##T VALUES('Level1'),('Level1'),('Level1')

EXEC #Level2

SELECT * FROM ##T

IF OBJECT_ID('tempdb.dbo.##T') IS NOT NULL BEGIN DROP TABLE ##T END

END

GO

CREATE PROC #Level2
AS
BEGIN

INSERT INTO ##T VALUES('Level2'),('Level2'),('Level2')

EXEC #Level3

END

GO

CREATE PROC #Level3
AS
BEGIN

INSERT INTO ##T VALUES('Level3'),('Level3'),('Level3')

END

You could also pass xml in and out of procedures

matteo
Starting Member

2 Posts

Posted - 2015-03-27 : 11:16:36

Thank you very much Michael for your replay.
Probably I made a mistake using SP instead of table function, since I need as output only a table. And in this way I can realized what I need.
To be honest I can't completly understand why there is this problem: as sayd I come from Access world and there is absolutly normal use query based on other query...
thanks again!

Kristen
Test

22859 Posts

Posted - 2015-03-27 : 15:50:19

You can do a Query based on a Query in SQL, but not [easily] based on a Stored Procedure.

Its not really like Access though ...

I remember (a long time ago!!) when we had a complex query to do in Access we would:

Do the inner part of the query, save it as a query.
DO the next level query, using the previous saved query for part of it.

I probably tried to do "something similar" when I moved to SQL, but I don't any long as I suppose I have learnt the "SQL way

One route that might help you, as you transition, would be to CREATE and then use a VIEW. You might find that similar to Access in a way that helps you.


CREATE VIEW MyView
AS
    SELECT Col1, Col2, Col3
    FROM MyTable
    WHERE ColX = 'FOO'
GO

then


SELECT Col2
FROM MyView
WHERE Col3='BAR'
ORDER BY Col1

If you want/need to use a temporary table with SProcs you would be better off with a #TEMP temporary table rather than an @TEMP Table Variable. @TEMP tables have very limited scope and capabilities (compared to full blow database tables) and you can't do things like inserting the results of an Sproc into them.

If you create a table in your outer level code e.g.


CREATE #MyTable
(
    Col1 varchar(10) NOT NULL,
    Col2 int,
    PRIMARY KEY
    (
        Col1
    )
)

you can then EXEC a Stored Procedure which, itself, can do:


INSERT INTO #MyTable
(
    Col1, Col2, ...
)
SELECT Abc1, Abc2, ...
FROM MyTable
WHERE ...

...
RETURN

Because the #TEMP table is created in the outer code it is "in scope" in the inner code. Once the outer code returns (to its caller) the #TEMP table will be automatically dropped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources