Calling a stored procedure from another

• swathi-324373

  SSC Journeyman

  Points: 77

  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.

• Julie Hargraves

  SSCarpal Tunnel

  Points: 4457

  More actions

  May 23, 2006 at 11:52 am

  #639567

  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

  May 23, 2006 at 12:10 pm

  #639569

  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

  SSChasing Mays

  Points: 617

  More actions

  May 24, 2006 at 12:24 pm

  #639879

  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