what's the correct way to use scope_identity() (and SQL in general)

[dzr]

One last questions... scope_identity()

I can't seem to find a detailed yet simple overview on using scope_identity()

My main questions is do I *have* to do it in a stored procedure?

Can I get away with an entire application without ever using a stored procedure? (as well as all the fancy stuff like Users & Roles).

 

[jbenson001]

From Books On Line:

Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Can I get away with an entire application without ever using a stored procedure? (as well as all the fancy stuff like Users & Roles).

Can you.. yes... should you.. no
I am a firm beliver in SPs for many reasons. Mainly they are faster and promote easy maintanability.

Jim