Is my beloved TSEQUAL() officially dead in 2005?

It seems my much loved TSEQUAL function was finally buried in SQL 2005.
Unless someone can suggest something better I guess I'm going to have to
resort to something like the code below. Any other suggestions would be
greatly apprieciated. I'll probably create a custom message to replace 532
so I can at least raise a consistent error. Anybody know the story behind
the demise of TSEQUAL? I know it's been undocumented for a while, but I was
always quite fond of it myself and came to rely on it. I know it's a sin to
depend on undocumented features, but it was just so handy I couldn't resist.
CREATE TABLE a(id int, col1 int, TS TIMESTAMP)
INSERT a (id, col1) VALUES(1,1)
DECLARE @.TS TIMESTAMP
DECLARE @.id INT
DECLARE @.RowCount INT
DECLARE @.Error INT
SELECT @.ID = id, @.TS = ts FROM a WHERE id = 1
--UPDATE a SET col1 = 2 WHERE id = @.id
--DELETE a WHERE id = @.id
UPDATE a SET col1 = 2 WHERE id = @.id AND TS = @.TS
SELECT @.Error = @.@.ERROR,
@.RowCount = @.@.ROWCOUNT
IF(@.Error = 0)
BEGIN
IF(@.RowCount = 0)
BEGIN
IF EXISTS(SELECT * FROM a WHERE id = @.id)
RAISERROR('Row not updated because it was changed', 16, 1)
ELSE
RAISERROR('Row is gone', 16, 1)
END
ELSE
PRINT 'Row updated'
END
ELSE
PRINT 'Error'Byron,
While I don't recommend TSEQUAL, which is undocumented,
it is available in my version of SQL Server 2005, and in fact, it
works better than in 2000. A bug I noted here
http://groups.google.com/groups/sea...al+useful+skass
is fixed.
Can you post a repro to show that it fails? Here is a repro where it
works for me:
if tsequal(2,2) select 1
if tsequal(-1, 33) select 1
Result in 2005 (version 9.00.2047.00):
1
Server: Msg 532, Level 16, State 2, Line 2
The timestamp (changed to 0x00000000ffffffff) shows that the row has
been updated by another user.
Result in 2000 (note extra result set):
1
Server: Msg 532, Level 16, State 2, Line 2
The timestamp (changed to 0x00000000ffffffff) shows that the row has
been updated by another user.
1
Byron wrote:

>It seems my much loved TSEQUAL function was finally buried in SQL 2005.
>Unless someone can suggest something better I guess I'm going to have to
>resort to something like the code below. Any other suggestions would be
>greatly apprieciated. I'll probably create a custom message to replace 532
>so I can at least raise a consistent error. Anybody know the story behind
>the demise of TSEQUAL? I know it's been undocumented for a while, but I wa
s
>always quite fond of it myself and came to rely on it. I know it's a sin t
o
>depend on undocumented features, but it was just so handy I couldn't resist
.
>CREATE TABLE a(id int, col1 int, TS TIMESTAMP)
>INSERT a (id, col1) VALUES(1,1)
>DECLARE @.TS TIMESTAMP
>DECLARE @.id INT
>DECLARE @.RowCount INT
>DECLARE @.Error INT
>SELECT @.ID = id, @.TS = ts FROM a WHERE id = 1
>--UPDATE a SET col1 = 2 WHERE id = @.id
>--DELETE a WHERE id = @.id
>UPDATE a SET col1 = 2 WHERE id = @.id AND TS = @.TS
>SELECT @.Error = @.@.ERROR,
> @.RowCount = @.@.ROWCOUNT
>IF(@.Error = 0)
>BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM a WHERE id = @.id)
> RAISERROR('Row not updated because it was changed', 16, 1)
> ELSE
> RAISERROR('Row is gone', 16, 1)
>END
>ELSE
> PRINT 'Row updated'
>END
>ELSE
>PRINT 'Error'
>
>|||Apparently you don't need it anymore (according to John Gallardo of the SQL
Server Engine team, in this thread:
http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:B43733C4-57BA-41EE-B932-8F17A1B29065@.microsoft.com...
> It seems my much loved TSEQUAL function was finally buried in SQL 2005.
> Unless someone can suggest something better I guess I'm going to have to
> resort to something like the code below. Any other suggestions would be
> greatly apprieciated. I'll probably create a custom message to replace
> 532
> so I can at least raise a consistent error. Anybody know the story behind
> the demise of TSEQUAL? I know it's been undocumented for a while, but I
> was
> always quite fond of it myself and came to rely on it. I know it's a sin
> to
> depend on undocumented features, but it was just so handy I couldn't
> resist.
> CREATE TABLE a(id int, col1 int, TS TIMESTAMP)
> INSERT a (id, col1) VALUES(1,1)
> DECLARE @.TS TIMESTAMP
> DECLARE @.id INT
> DECLARE @.RowCount INT
> DECLARE @.Error INT
> SELECT @.ID = id, @.TS = ts FROM a WHERE id = 1
> --UPDATE a SET col1 = 2 WHERE id = @.id
> --DELETE a WHERE id = @.id
> UPDATE a SET col1 = 2 WHERE id = @.id AND TS = @.TS
> SELECT @.Error = @.@.ERROR,
> @.RowCount = @.@.ROWCOUNT
> IF(@.Error = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM a WHERE id = @.id)
> RAISERROR('Row not updated because it was changed', 16, 1)
> ELSE
> RAISERROR('Row is gone', 16, 1)
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>