在简单存储过程中正确使用 SCOPE_IDENTITY 函数 [英] 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.

以下 SCOPE_IDENTITY() 的使用是否正确?

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;

编辑

我已将代码编辑为以下内容:

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;

推荐答案

似乎这是最好的方法 - 可以看到一些建议仅使用 RETURN 作为传达状态或错误的方式的参考所以 OUTPUT 参数是更好的做法:

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;

这篇关于在简单存储过程中正确使用 SCOPE_IDENTITY 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！