Return scope_identity from a stored procedure in SQL itself how? RSS

• • Reply
  

  asplearning

  Participant

  909 Points

  952 Posts

  Return scope_identity from a stored procedure in SQL itself how?

  Jun 13, 2012 02:39 AM|LINK

  Hello everyone, I have this stored procedure:

  ALTER PROCEDURE [dbo].[Test]
  	
  AS
  	INSERT INTO [dbo].[Testing]
             ([Body]
            )
       VALUES
  			(1)
  
  	RETURN Scope_identity()

  I want to call this stored procedure in another stored procedure and get the identity number out of Test stored procedure. Something like:

  Create PROCEDURE dbo.Test2
  	
  
  AS
  	Declare @RecordID int
  	
  	Set @RecordID = EXEC dbo.Test
  
  	Print @RecordID
  
  	RETURN 

  But this doesn't work. Please show me how. 

  Thanks. 
  
  

  
  
  

  • Moved by mbanavige on Jun 14, 2012 01:09 AM
• • Reply
  

  santosh.jagd...

  Star

  7625 Points

  1454 Posts

  Re: Return scope_identity from a stored procedure in SQL itself how?

  Jun 13, 2012 04:06 AM|LINK

  instead of returning you should declare output parameter as follows

  CREATE PROCEDURE [dbo].[INS_FlightNewLeg]
  @leg1 int,
  @leg2 int,
  @leg3 int,
  @NewLegID int OUTPUT
  
  AS
  BEGIN
  SET NOCOUNT ON;
  INSERT INTO FlightLegs
  Values(
  @leg1,@leg2,@leg3
  )
  SELECT @NewLegID = SCOPE_IDENTITY()
  END

  Santosh J
  MCP
  
  • Marked as answer by asplearning on Jun 13, 2012 11:52 PM
• • Reply
  

  asplearning

  Participant

  909 Points

  952 Posts

  Re: Return scope_identity from a stored procedure in SQL itself how?

  Jun 13, 2012 04:45 AM|LINK

  Hello santosh, just quickly show me how I can get that value in the second stored procedure that called this stored procedure. Can it be something like:

  Create PROCEDURE dbo.Test2
          
  
  AS
          Declare @RecordID int
          
          Set @RecordID = EXEC [dbo].[INS_FlightNewLeg]
  
          Print @RecordID
  
          RETURN

  Thanks. 
  
  

  • Edited by asplearning on Jun 13, 2012 04:45 AM
  • Edited by asplearning on Jun 13, 2012 11:52 PM
  • Edited by asplearning on Jun 13, 2012 11:52 PM
• • Reply
  

  santosh.jagd...

  Star

  7625 Points

  1454 Posts

  Re: Return scope_identity from a stored procedure in SQL itself how?

  Jun 13, 2012 05:08 AM|LINK

  here is the sample code

  declare @rowCount int
  exec yourStoredProcedureName @outputparameterspOf = @rowCount output

  Santosh J
  MCP
  
  • Marked as answer by asplearning on Jun 13, 2012 11:52 PM
• • Reply
  

  tjaank

  Contributor

  6688 Points

  1204 Posts

  Re: Return scope_identity from a stored procedure in SQL itself how?

  Jun 13, 2012 05:29 AM|LINK

  i would recommend you to use Functions for it .. as one of the main difference between functions and stored procudures is that you can not use stored procedures in inline queries, where as you can use functions for that .. it would be somthing like this:

  SELECT @RecordID = [dbo].[fn_INS_FlightNewLeg]

  
  
  

  Talha Ashfaque -- Code Shode

  ---

  Please Mark as Answer if this post helps you!
• • Reply
  

  asplearning

  Participant

  909 Points

  952 Posts

  Re: Return scope_identity from a stored procedure in SQL itself how?

  Jun 13, 2012 11:52 PM|LINK

  Thanks, santosh. That works.

  Regards;

• • Reply
  

  asplearning

  Participant

  909 Points

  952 Posts

  Re: Return scope_identity from a stored procedure in SQL itself how?

  Jun 13, 2012 11:53 PM|LINK

  hello tjaank, I want to use a function but I need a tempory table in my procedure and function doesn't support temporary table. 

  Thanks anyway.