How to get identity value using scope_identity

[sujosh]

Is there an example of an INSERT and the SELECT that used SCOPE_IDENTITY to return the identity field ?  The examples I am finding use stored procedures..

Thanks

 

[SQLSister]

The syntax of the insert and select is the same whether you use stored procedures or not. I am at a loss as to why you wouldn't use a stored procedure for this anyway especially if you intend to use that value to insert records into a related table. In a stored procedure you can use transaction processing to make sure the whole transaction is committed or rolled back. Plus stored procedures are faster than dynamic queries since the execution plan can be reused rather than created again.

Questions about posting. See faq183-874

 

[sujosh]

Thanks SQLSister!