return output value from dynamic sql
I was looking for a way to return an Identity value from a dynamic sql statement.
The simplest way is to pass an output parameter along with the sql:
Dim arrParams(0) As SqlParameter
arrParams(0) = New SqlParameter("@iSCID", SqlDbType.Int)
arrParams(0).Direction = ParameterDirection.Output
Then add the following to the end of the sql statement: ” ; Set @iSCID = @@Identity ”
You can then reference the value in this manner (I use the SqlHelper class from MS):
SqlHelper.ExecuteNonQuery(cn, CommandType.Text, strSql, arrParams)
iSCID = arrParams(0).Value
This page has a lot of great information.
The Curse and Blessings of Dynamic SQL
UPDATE: I ran into a situation where I was using triggers for an audit log that caused the above method to return weird @@Identity values. Turns out, if your trigger inserts into another table, that @@Identity will be returned, not the @@Identity from the intended table.
on the side note: you should NEVER be using @@IDENTITY to get the last inserted identity from a table. this has been shouted down from the havens numerous times. @@IDENTITY will return the last inserted identity from ANY table of TRIGGER within the transaction session. you MUST use SCOPE_IDENTITY(). even IDENT_CURRENT is dangerous from the BOL about the dangers of using anything but SCOPE_IDENTITY():
SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.
Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.
@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.
SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. 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.
Trackback URI |