why does SELECT SCOPE_IDENTITY() return null?

Question

in what case does SELECT SCOPE_IDENTITY() return null?

i am doing this:

Set rs = cn.Execute("SELECT SCOPE_IDENTITY()", , adCmdText)
capture_id = rs.Fields(0)

and i getting capture_id=null

Answer 1

According to MSDN, "The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope."

Answer 2

To be more specific, SCOPE_IDENTITY() only returns the latest identity value generated within the same scope as the SCOPE_IDENTITY() call. In the example you posted, the SCOPE_IDENTITY() call is the only statement in the batch (hence the only statement within the current scope) so it will return null. If you want to get the latest identity value generated for a particular table, try IDENT_CURRENT('tablename'). This is not subject to scope, it just gives you the latest identity value generated for the given table.