[thelist] Using @@Identity

[Joshua Olson]

> -----Original Message-----
> From: Ken Schaefer
> Sent: Tuesday, August 23, 2005 10:15 PM
> 
> ENOUGH!
> 
> 
> a) use Scope_Identity not @@IDENTITY.

Good advice.  Using the technique I suggested (which you recommend against
:-)), the change from @@identity to SCOPE_IDENTITY() is quite simple:

CREATE TRIGGER mytable_insert ON dbo.mytable
FOR INSERT
AS SELECT SCOPE_IDENTITY() AS id

I was also thinking, just now, that you would also:

CREATE TRIGGER mytable_insert ON dbo.mytable
FOR INSERT
AS SELECT inserted.id AS id FROM inserted
-- assuming that id is the identity field in question!

> b) I would recommend against what Joshua has suggested. It 
> might make sense in the CF world

Which it does, since "typical" CF querying methods (using cfquery) are not
very rich in terms of exposing connection and parameter options.

> , but in ASP/ASP.NET you 
> should return an output parameter (which is very lightweight), 
> or use ExecuteScalar if you really want to return a recordset.
> ExecuteScalar is useful if you only want to return a recordset 
> with a single row and a single column. I would recommend 
> using an Output Parameter

This really makes sense.  Thank you for the brief overview.  I would like to
make one additional argument AGAINST the technique I suggested--it's not
very portable.  Some of the mainstream databases are just now beginning to
support triggers and stored procedures.  But, of the big 3 databases (IMO,
these are MS SQL Server, MySQL, and PostgreSQL), my technique only works on
MS SQL Server.  If you want to use a generalized system that works on all
databases, then you'll want to avoid things like Triggers and lean towards
Stored Procedures and Transactions.

Just my 2cents.

<><><><><><><><><><>
Joshua L. Olson
WAE Tech Inc.
http://www.waetech.com/
Phone: 706.210.0168 
Fax: 413.812.4864

Monitor bandwidth usage on IIS6 in real-time:
http://www.waetech.com/services/iisbm/