开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜