Geek asks and answers

Proper use of the SCOPE_IDENTITY function in a simple stored procedure

I would just like to send some information from a simple client to a log file, and then use the identifier created for further processing.

Is the SCOPE_IDENTITY()following used correctly :

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 edited the code for 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;
+5
source share
3 answers

This seems to be the best approach - you can see several links that are recommended to be used RETURNas a way to convey status or errors, so it’s better to use the parameter OUTPUT:

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;
+27
source

You can also use SCOPE_IDENTITYin two separate statements, for example:

, , ,

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

SCOPE_IDENTITY() Where, :

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

, scope_identity .

+3
RETURN SCOPE_IDENTITY()

returns 0with dapper.
I replaced it with

SELECT SCOPE_IDENTITY()

and now it returns the actual value.

0
source

More articles:


All Articles