Getting IDENTITY from dynamic INSERT

Question

In a stored procedure I am dynamically creating a query with a INSERT. This is done in order to force default values (like with @name if it is 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 will be 0 no matter.
How can I retrieve the IDENTITY in a safe manner even if another thread is running the same stored procedure simultaneously?

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

Though a few points:
- assuming this is a simplified example as there doesn't seem to be a need to use dynamic SQL in this example
- assuming you're not going to store real passwords in plain text in the db!

Answer 2

Alternatively, you can use the OUTPUT clause with the INSERT statement. That will cause the dynamic statement, and, consequently, the system stored procedure used to invoke it, to return a rowset (one row in your case). You can grab at the chance and insert the rowset into a table variable, and then read the value.

Basically, it might look like this:

SET @sql = 'INSERT INTO table (...) OUTPUT inserted.ID VALUES (...)';
DECLARE @ScopeIdentity (ID int);
INSERT INTO @ScopeIdentity
  EXEC sp_executesql @sql;
SELECT @id = ID FROM @ScopeIdentity;