返回刚刚添加的行的id

In a similar vein to my previous question I again ask the SO guys for your collective wisdom and help.

与我之前提出的问题类似,我再次向SO人员询问你的集体智慧和帮助。

In a stored procedure and after passing some checks I need to insert a new row and return the newly created id for it. The check if a row exists works so it is the bit after that which I am undecided upon.

在存储过程中,在传递一些检查之后,我需要插入一个新行并返回新创建的id。检查是否存在一行是有效的,所以它是在我未定的之后的位。

The table has two important columns: The LocationID and the CaseID. The CaseID is autoincrementing, so when you add insert a new locationid it will automatically rachet up.

该表有两个重要的列:LocationID和CaseID。 CaseID是自动递增的,因此当您添加插入新的locationid时,它将自动增加。

I currently have this:

我目前有这个:

-- previous checks for existance of CaseID

IF @CaseID IS NULL
BEGIN
    INSERT INTO
        Cases(LocationID)
    VALUES
        (@LocationID)

    -- what now?
END

I was thinking of performing a @CaseID = (SELECT blah) statement immeadiately after but I was wondering if there is a better way?

我想在之后立即执行@CaseID =(SELECT blah)语句,但我想知道是否有更好的方法?

Is there a better way? How would you do this?

有没有更好的办法?你会怎么做?

5 个解决方案

#1

SELECT @CaseID = SCOPE_IDENTITY()

In fact, you can just do (if that's the end of the stored proc.):

事实上,你可以这样做(如果这是存储过程的结束):

SELECT SCOPE_IDENTITY()

(The OUTPUT clause is only available in SQL Server 2005 onwards...)

(OUTPUT子句仅在SQL Server 2005及更高版本中可用...)

Ref: SCOPE_IDENTITY

#2

scope_identity()

#3

SELECT SCOPE_IDENTITY()

#4

As others mentioned, SCOPE_IDENTITY() is the way to go, though some ORM tools provide this functionality as well.

正如其他人提到的,SCOPE_IDENTITY()是可行的方法,尽管一些ORM工具也提供此功能。

The only thing you need to remember is SCOPE_IDENTITY() will return the last identity key value generated during the current session only. This is useful in filtering out new keys which may have been created by other clients simultaneously. SELECT @@IDENTITY will return the last key generated by any client/session.

您唯一需要记住的是SCOPE_IDENTITY()将返回仅在当前会话期间生成的最后一个标识密钥值。这对于过滤掉可能由其他客户端同时创建的新密钥很有用。 SELECT @@ IDENTITY将返回任何客户端/会话生成的最后一个密钥。

#5

You need to use the OUTPUT clause

您需要使用OUTPUT子句

http://blog.jemm.net/articles/databases/how-to-using-sql-server-2005s-output-to-return-generated-identity/

...which, as pointed out, is only available in sqlserver 2005. Plz disregard.

......正如所指出的那样,只能在sqlserver 2005中使用.Plz无视。

#1

SELECT @CaseID = SCOPE_IDENTITY()