Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Getting a return value when calling a stored procedure

Posted on 2007-10-03
3
Medium Priority
?
169 Views
Last Modified: 2010-03-19
Dear Experts,

I'm calling a stored procedure from within a stored procedure and need to get the return value.

I'm calling EXEC CreateCompany @Company_Name, @EmployeeNo_ID, @Creator, @Created

within CreateCompany I do this to get the new ID...

SELECT @@IDENTITY
Return @@IDENTITY

How can I use this value in my original stored procedure?

Any help would be appreciated.

Thanks

NIck
0
Comment
Question by:nkewney
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 total points
ID: 20008907
first replace  @@IDENTITY  with SCOPE_IDENTITY ()
In order to get the return value

declare @RetValue int
EXEC @RetValue = CreateCompany @Company_Name, @EmployeeNo_ID, @Creator, @Created
SELECT @RetValue
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20008909
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 800 total points
ID: 20008914
note that SELECT @@IDENTITY + RETURN @@IDENTITY are double work, and usually not needed.
also, as from SQL 2000, you should NOT use @@identity, but SCOPE_IDENTITY() function instead.

anyhow:

CREATE PROCEDURE CreateCompany
( ....
, @NewID int OUTPUT
)
AS
  SET NOCCOUNT ON

  INSERT ...
 
  SET @NewID = SCOPE_IDENTITY()
GO


and your code goes, in SQL:

DECLARE @new_id INT

EXEC CreateCompany @Company_Name, @EmployeeNo_ID, @Creator, @Created, @new_id OUTPUT

select @new_id new_generated_id


0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question