Scope_Identity - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Scope_Identity

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2009-05-11 : 11:08:06

Hi,

I am having problems returning the record ID. I normally do something like this:

Create procedure spExample
Insert into .. blah blah blah
...

Select Scope_Identity

Then in my code: Object obj = command.executeScalar(); .. blah blah blah
Which works fine.
However, I have this sp I am trying to get the ID from:

@TargetID int,
@rptDate datetime,
@title varchar(64),
@grpName varchar(64),
@FileName varchar(100),
@newFileName as varchar (100) = null

AS
DECLARE @ReportID as int

BEGIN
SET NOCOUNT ON;

INSERT INTO tblReports
(Date
,Title
,GroupReportName
,ReportType)
VALUES
(@rptDate,
@title,
@grpName,
'NonSym')
SET @ReportID = @@IDENTITY
END

BEGIN
INSERT INTO tblSections
( TargetID, ReportID, ViewCount )
VALUES
( @TargetID, @ReportID , 0 )
END
BEGIN
IF @TargetID > 0 AND NOT EXISTS
(SELECT TargetID FROM tblTargets WHERE TargetID = @TargetID)
INSERT INTO tblTargets (TargetID) VALUES (@TargetID)
END
BEGIN
SET @newFileName = (convert(varchar(50), @reportID) + '_' + @FileName)
END
BEGIN
UPDATE tblReports
Set FileName = @newFileName
Where ReportID = @ReportID
END

How do I get back the ReportID? I tried using the Scope_Identity, but that didn't work ...

Any help appreciated

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-11 : 11:09:53

SET @ReportID = SCOPE_IDENTITY()

And then add @ReportID as an output parameter to your stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 11:10:58

use a OUTPUT parameter and return through it. or just use RETURN @ReportID to return value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 11:11:53

also see this

http://www.sqlteam.com/article/stored-procedures-returning-data

ann
Posting Yak Master

220 Posts

Posted - 2009-05-11 : 11:13:10

Do I do this after I've the SET @ReportID = @@IDENTITY?

And in my code, do I just do a datareader and read it out or is it like executeScalar()?

Sorry, I've never done an output param

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 11:22:58

quote:
Originally posted by ann

Do I do this after I've the SET @ReportID = @@IDENTITY?

And in my code, do I just do a datareader and read it out or is it like executeScalar()?

Sorry, I've never done an output param

you need to use SCOPE_IDENTITY instead of @@IDENTITY and then use RETURN after that to return value. or you could use OUTPUT parameter in procedure and just set its value to this. then in your application create an output parameter to receive this value.

ann
Posting Yak Master

220 Posts

Posted - 2009-05-11 : 11:25:26

Thanks - got it working. The link was a great help :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-11 : 11:41:00

I wouldn't recommend RETURN for this, just use an OUTPUT parameter. RETURN should be used for sending stored procedure status and not for application data. Plus RETURN will prematurely exit the stored procedure if you don't put it in the right place.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources