CODEWITHSUNDEEP
sql-serverstored-procedures
whytheq
whytheq

Reputation: 35557

Correct use of SCOPE_IDENTITY function within simple stored procedure

I'd like to simply send some information from a simple client to a log file and then use the identity created for further processing.

Is the following use of SCOPE_IDENTITY() correct?

CREATE PROCEDURE [dbo].[LogSearch]
    @userName       VARCHAR(50),
    @dateTimeStart  DATETIME        
AS
BEGIN
SET NOCOUNT ON;


    INSERT INTO [WH].[dbo].[tb_Searches]
            (
            [UserName],
            [DateTimeStart]
            )
    SELECT  @userName, 
        @dateTimeStart;

    SELECT SCOPE_IDENTITY() AS ProfileKey;

END;

EDIT

I've edited code to the following:

ALTER PROCEDURE [dbo].[LogSearch]
    @userName   VARCHAR(50),
    @dateTimeStart  DATETIME
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [WH].[dbo].[tb_Searches]
            (
            [UserName],[DateTimeStart]
            )
    VALUES  (@userName, @dateTimeStart);

    RETURN SCOPE_IDENTITY();

END;

Upvotes: 7

Views: 84626

Answers (3)

Carlos A Merighe - Utah
Carlos A Merighe - Utah

Reputation: 135

RETURN SCOPE_IDENTITY()

is returning a 0 with Dapper.
I replaced it with 

SELECT SCOPE_IDENTITY()

and now it is returning the actual value.

Upvotes: 5

Alex
Alex

Reputation: 31

You can also use SCOPE_IDENTITY in two separate statements, such as:

Assuming that the table has an identity field of course

Insert into [ABCTable]
([A], [B])
select 'WhateverA', 'WhateverB'

Then use the function SCOPE_IDENTITY() in the Where clause, like:

Update [ABCTable] Set [A] = 'UpdateA', [B] = 'UpdateB'
Where IdentityField = SCOPE_IDENTITY()

Thus, the scope_identity is a reliable link to the record just inserted.

Upvotes: 3

whytheq
whytheq

Reputation: 35557

Seems like this is the best approach - can see a few references advising to only use RETURN as a way of communicating state or errors so an OUTPUT parameter is better practice:

ALTER PROCEDURE [dbo].[LogSearch]
    @userName      VARCHAR(50),
    @dateTimeStart DATETIME,
    @searchID      INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [WH].[dbo].[tb_Searches]
                (
                UserName,
                DateTimeStart
                )
    VALUES  
                (
                @userName, 
                @dateTimeStart
                );

    SET @searchID = SCOPE_IDENTITY();

END;

Upvotes: 32

Related Questions