Scope_Identity returns the identity value inserted in the current scope(regardless of a table) and a scope means a module,stored procedure,trigger,function or batch. (In your case you will get value as 0 as there are no changes in identity column in current scope).
To achieve what u want 1.You can use @@Identity which returns Identity value inserted regardless of the table and scope(so it may help) 2.Ident_Current('tableName') which return the last identity value generated for a specific table in any session and scope. 3.Create Stored Procedure and put your insert query followed by Select Scope_Identity() and front end execute Stored Procedure using the same syntax.
Hope It Helped. Please Mark answer as Accepted Answer.
Sukesh MarlaPosted Aug 12, 2012, 1:57 AM
Hope It Helped. Please Check This is Currect Answer.
Sukesh MarlaPosted Aug 12, 2012, 2:50 AM
bagzliPosted Aug 12, 2012, 2:05 AM
bagzliPosted Aug 12, 2012, 1:54 AM
Sukesh MarlaPosted Aug 12, 2012, 1:35 AM
Can u do like this
string sqlStatement = InsertQuery + ";SELECT SCOPE_IDENTITY()";
Even this will work.
Hope It Helped. Please Mark answer as Accepted Answer.
bagzliPosted Aug 12, 2012, 1:27 AM
Sukesh MarlaPosted Aug 12, 2012, 1:12 AM
(In your case you will get value as 0 as there are no changes in identity column in current scope).
To achieve what u want
1.You can use @@Identity which returns Identity value inserted regardless of the table and scope(so it may help)
2.Ident_Current('tableName') which return the last identity value generated for a specific table in any session and scope.
3.Create Stored Procedure and put your insert query followed by Select Scope_Identity() and front end execute Stored Procedure using the same syntax.
Hope It Helped. Please Mark answer as Accepted Answer.