Calling a stored procedure from another

Question

Calling a stored procedure from another

swathi-324373

SSC Enthusiast

Points: 107
More actions
May 23, 2006 at 11:19 am

#366263

Hi All,
could anyone help me with an example as to how to call a stored procedure from another procedure.
I need to call sprocA, which inserts values in DB and also retuns an integer value, pass this integer value to sprocB and perform some inserts.
Thanks in advance,
vnswathi.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 1

To call a stored procedure from another, you just use the EXEC command (assuming both stored procedures are in the same database):

EXEC sprocB @IntegerValueVariableNameInSprocB = @IntegerValueVariableNameInSprocA

John Rowan SSC Guru Points: 56440 More actions · Answer 2

I would recommend adding some error handling to the nested stored procedure call.

DECLARE @ReturnValue int

EXEC @ReturnValue = proc_Call_Some_StoredProcedure

SET @intSQLErrorNumber = COALESCE(NULLIF(@ReturnValue,0),@@ERROR,1001)

IF @intSQLErrorNumber <> 0 GOTO ErrorHandler

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ross Petersen Say Hey Kid Points: 677 More actions · Answer 3

I agree with the above 2 suggestions - using error handling is a good idea.

In terms of performance, it is a good idea not to name a SP with the first two letters being sp or SP because SQL Server will automatically look in the Master DB first to see if the SP is there befor eit looks in the current DB.

Kind regards

Ross