sql-server-2005 - 从动态 INSERT 获取 IDENTITY

Question

在存储过程中，我使用 INSERT 动态创建查询。这样做是为了强制默认值(就像@name 如果它是NULL)。

SET @sql = 'INSERT INTO table (username, password'
    + CASE @name IS NULL THEN '' ELSE ',name' END
    + ') VALUES (''root'',''gelehallon''' +
    + CASE @name IS NULL THEN '' ELSE ',''@name''' END
    + ')'
EXEC sp_executesql @sql

SET @id = SCOPE_IDENTITY()

无论如何，@id 都会是 0。
即使另一个线程同时运行相同的存储过程，我如何以安全的方式检索 IDENTITY？

Answer 1

SET @sql = 'INSERT INTO table (username, password) VALUES (@username,@pwd) 
    SELECT @id = SCOPE_IDENTITY()'
EXEC sp_executesql @sql, 
    N'@username VARCHAR(50), @pwd VARCHAR(50), @id INTEGER OUTPUT', 
    'root', 'gelehallon', @id OUTPUT

-- @id now has SCOPE_IDENTITY() value in

虽然有几点:
- 假设这是一个简化的例子，因为在这个例子中似乎不需要使用动态 SQL
- 假设您不打算在数据库中以纯文本形式存储真实密码!