与我的上一个问题类似，我再次请求SO人员提供集体智慧和帮助。
在存储过程中，通过一些检查后，我需要插入一个新行并为其返回新创建的 id。 检查一行是否存在是有效的，所以这是我尚未决定的之后的位。
该表有两个重要的列：LocationID 和CaseID。 CaseID 是自动递增的，因此当您添加插入新的 locationid 时，它会自动递增。
我目前有这样的情况：
-- previous checks for existance of CaseID
IF @CaseID IS NULL
BEGIN
INSERT INTO
Cases(LocationID)
VALUES
(@LocationID)
-- what now?
END
我正在考虑立即执行 @CaseID = (SELECT blah) 语句，但我想知道是否有更好的方法？
有没有更好的办法？ 你会怎么做？
In a similar vein to my previous question I again ask the SO guys for your collective wisdom and help.
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.
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.
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?
Is there a better way? How would you do this?
发布评论
评论（5）
事实上，您可以这样做（如果这是存储过程的结尾。）：（
OUTPUT 子句仅在 SQL Server 2005 及以上版本中可用...）
参考：SCOPE_IDENTITY
In fact, you can just do (if that's the end of the stored proc.):
(The OUTPUT clause is only available in SQL Server 2005 onwards...)
Ref: SCOPE_IDENTITY
范围_身份（）
scope_identity()
选择 SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY()
正如其他人提到的，SCOPE_IDENTITY() 是可行的方法，尽管一些 ORM 工具也提供此功能。
您唯一需要记住的是 SCOPE_IDENTITY() 将仅返回当前会话期间生成的最后一个身份密钥值。 这对于过滤掉其他客户端可能同时创建的新密钥很有用。 SELECT @@IDENTITY 将返回任何客户端/会话生成的最后一个密钥。
As others mentioned, SCOPE_IDENTITY() is the way to go, though some ORM tools provide this functionality as well.
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.
您需要使用 OUTPUT 子句
http://blog.jemm.net/articles/databases/how-to-using-sql-server-2005s-output-to-return- generated-identity/
...其中正如所指出的，仅在 sqlserver 2005 中可用。请忽略。
You need to use the OUTPUT clause
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.