SQL Server 7.0 equiv of IDENTITY_CURRENT

[Jack Turnbull]

Hi,
I am writing code for the subject version and am trying to find the
equivalent of

SELECT SaveDate FROM Performance
WHERE IDENTITYCOL = IDENT_CURRENT('Performance')

In other words I need to return a field in the last row of a table. v7.0
does not recognise IDENT_CURRENT

Any offers?

(btw, cannot upgrade - not my database)

Thanks

[David Portas]

Jack Turnbull wrote:

Hi,
I am writing code for the subject version and am trying to find the
equivalent of

SELECT SaveDate FROM Performance
WHERE IDENTITYCOL = IDENT_CURRENT('Performance')

In other words I need to return a field in the last row of a table. v7.0
does not recognise IDENT_CURRENT

Any offers?

(btw, cannot upgrade - not my database)

Thanks

DBCC CHECKIDENT ('tablename', NORESEED) will give you the same value as
IDENT_CURRENT() but that won't necessarily be the last value inserted
in the current connection and it may not even be a value that exists in
the table. Those same caveats apply to IDENT_CURRENT(). IDENT_CURRENT()
is of very little use at all IMO but it is often MIS-used.

Porbably the better alternatives are:

SELECT TOP 1 SaveDate
FROM Performance
ORDER BY identitycol DESC;

SELECT SaveDate
FROM Performance
WHERE identitycol = @@IDENTITY;

In SQL Server 2000, use SCOPE_IDENTITY() in preference to @@IDENTITY.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Jack Turnbull]

Thanks David,
Your first alternative worked great!
Cheers,
Jack
"David Portas" wrote in message
news:11*********************@m79g2000cwm.googlegro ups.com...

Jack Turnbull wrote:

>Hi,
I am writing code for the subject version and am trying to find the
equivalent of

SELECT SaveDate FROM Performance
WHERE IDENTITYCOL = IDENT_CURRENT('Performance')

In other words I need to return a field in the last row of a table. v7.0
does not recognise IDENT_CURRENT

Any offers?

(btw, cannot upgrade - not my database)

Thanks

DBCC CHECKIDENT ('tablename', NORESEED) will give you the same value as
IDENT_CURRENT() but that won't necessarily be the last value inserted
in the current connection and it may not even be a value that exists in
the table. Those same caveats apply to IDENT_CURRENT(). IDENT_CURRENT()
is of very little use at all IMO but it is often MIS-used.

Porbably the better alternatives are:

SELECT TOP 1 SaveDate
FROM Performance
ORDER BY identitycol DESC;

SELECT SaveDate
FROM Performance
WHERE identitycol = @@IDENTITY;

In SQL Server 2000, use SCOPE_IDENTITY() in preference to @@IDENTITY.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--