TimeStamp in SQL Server 2000
I have a table named Table1
which contains an ID
and TimeStamp
.
Table structure
ID TimeStamp
1 0x0000000000047509
But when I compare the combination of these fields, it always shows false. What is the reason for this?
My Query is :
DECLARE @ID int
DECLARE @TimeStamp timestamp
SET @ID = 1
SET @TimeStamp = 0x0000000000047509
If EXISTS(SELECT 1 FROM Table1 WHERE ID = @ID AND TimeStamP = @TimeStamp)
BEGIN
SELECT 1 AS RetVal
END
ELSE
BEGIN
SELECT -1 AS RetVal
END
My stored procedure is as follows
CREATE PROCEDURE [dbo].[Check] (
@XMLDoc ntext
)AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @ID bigint
DECLARE @TimeStamp timestamp
DECLARE @hDoc int
EXEC sp_xml_PrepareDocument @hDoc OUT, @XMLDoc
SELECT @ID = ID
,@TimeStamp = [TimeStamp]
FROM OPENXML (@hdoc,'/XML')
WITH ( ID bigint 'ID'
,[TimeStamp] timestamp 'TStamp')
IF @@ERROR<>0
BEGIN
EXEC sp_xml_Re开发者_运维知识库moveDocument @hDoc
SELECT -620 AS RetVal
RETURN
END
IF NOT EXISTS(SELECT 1 FROM Table1 WHERE ID= @ID AND Timestamp = @TimeStamp )
BEGIN
SELECT -1 AS RetVal
END
ELSE
BEGIN
SELECT 1 AS RetVal
END
END
That's odd, the query works fine for me in SQL Server 2005, compatibility mode 80.
The only thing that jumps out to me is that Timestamp
is a reserved word, so to be on the safe side you might want to add brackets around Timestamp
to escape it as follows:
If EXISTS(SELECT 1 FROM Table1 WHERE [ID] = @ID AND [TimeStamP] = @TimeStamp) ...
@TimeStamp is not being resolved correctly from the XML. Try CASTing it to binary(8).
The stored proc does not match the query you posted earlier
精彩评论