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

General SQL Server Forums

New to SQL Server Programming

Nested Stored Procedures

Author

Topic

Plotin
Starting Member

21 Posts

Posted - 2005-12-05 : 19:52:59

Hi Out There

The attempt to create the following stored procedure, which is supposed to call the previously created stored procedures inside it,
causes the following errors.

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferIDTwo'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferIDThree'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.

Create Procedure spPrepareOfferSimulation
@OfferIDOne int
As
exec spPopulateOfferTables @OfferID
go
exec spPopulateOfferProduct @OfferID
go
exec spPopulateOfferDictionary @OfferID
go
exec spPopulateOfferCondition @OfferID
go
exec spPopulateOfferError @OfferID
go
exec spPopulateOfferLimit @OfferID
go
exec spPopulateOfferQA @OfferID
go

Can someone provide me with some productive input to fix this mess?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-12-05 : 19:59:08

Remove all but the last "GO" from your procedure. GO is not a SQL command, it is a batch separator. It signals the end of the CREATE PROCEDURE batch, that's why you're receiving the error.

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-12-05 : 23:52:21

Hi,
* CREATE PROCEDURE * refer this topic from BOL .

:-)
Regards

Andy Davis
Activecrypt Team
--------------------------------------------
SQL Server Encryption Software
http://www.activecrypt.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-06 : 00:00:18

Why do you want to call multple procedures inside a procedure?. Instead call them seperately

Madhivanan

Failing to plan is Planning to fail

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-12-06 : 00:08:39

>>Why do you want to call multple procedures inside a procedure?.

Network overhead

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

Kristen
Test

22859 Posts

Posted - 2005-12-06 : 03:57:03

"Why do you want to call multple procedures inside a procedure?"

We do this to split large Sprocs so that the individual parts can have separate cached query plans

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-06 : 04:04:28

Well. That sounds good

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-12-06 : 04:53:44

Actually "large Sprocs" is probably the wrong definition. We more normally use this approach where an Sproc has a number of different execution paths, and we don't want the cached query plan to favour any particular "logic route" though the SProc.

IF @foo = 'BAR' EXEC dbo.SomeOtherSproc_A
IF @bar = 'FOO' EXEC dbo.SomeOtherSproc_B

and so on

Kristen

Plotin
Starting Member

21 Posts

Posted - 2005-12-06 : 07:55:47

Followed your advice robvolk and it works just fine

Thanks to all of you for your input

Plotin

Plotin
Starting Member

21 Posts

Posted - 2005-12-06 : 08:01:27

Hi madhivanan

The reason I am calling multiple store procedures from one is simply to learn about nested stored procedures.
Every individual stor proc populates a table,necessary to be done in order to do some work on them.Later on I will do all this work to be done from one stor proc and compare the performance of it against the approach of call individual ones from a single stor proc.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 00:53:36

Thats really good

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources