none
TVP with timestamp field

    Question

  • I need to pass timestamp values using table valued parameters. Since you cannot insert into a timestamp field, i had to declare the type of the timestamp field as varchar and cast it to timestamp in my queries.

    Is there a better way of doing this? Should i be worried about the cost of the cast function?

     


    Anonymous
    Friday, August 12, 2011 3:18 PM

Answers

  • Hi phbuser,

    According to your description, i have demonstrated the scenario as following statements:

    USE tempdb 
    GO
    
    --create a table
    CREATE TABLE dbo.TTimeStamp (
    	col1 char (10) NOT NULL ,
    	myTimeStamp timestamp NOT NULL 
    )
    GO
    
    Insert into TTimestamp (col1) values('hello')
    Insert into TTimestamp (col1) values('no')
    GO
    
    
    --create a PROCEDURE
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[sp_TimeStamp]
     @iTemp timestamp
    AS
    BEGIN
      SET NOCOUNT ON;
      
    SELECT CASE WHEN @iTemp=myTimeStamp THEN 1 ELSE 0 END FROM TTimestamp
    
    END ;
    
    GO
    
    --exec
    DECLARE @itemp timestamp
    SET @itemp=0x0000000000000924
    EXEC sp_TimeStamp @itemp
    


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by Stephanie Lv Monday, August 22, 2011 9:27 AM
    Tuesday, August 16, 2011 5:57 AM

All replies

  • Ah consider that a "TimeStamp" typed column is NOT a date/time structure, it's an integer that's bumped when the data is changed. In the olden days (IIRC) it WAS a DateTime value but that changed long ago.

    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Friday, August 12, 2011 3:30 PM
  • Hi phbuser,

    >>Since you cannot insert into a timestamp field, i had to declare the type of the timestamp field as varchar and cast it to timestamp in my queries.

    Since the timestamp filed (By default, binary(8) for non nullable and varbinary(8) for nullable) in the table stands for the row version which is changed by SQL Server automatically while any update made to the row. For instance, you can't specify the value for the timestamp field while you insert a row into the table, which is generated automatically by database engine.

    What is the purpose of inserting into a value in the table which is timestamp field? If it can be changed by yourself, it will not be the row version and lose its significance.

    For passing a timestamp, please take a look at the KB article below:
    http://support.microsoft.com/kb/249819
    .


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, August 15, 2011 3:02 AM
  • I probably did not explain well enough. I am sending data to a stored procedure using table valued parameters. In the stored procedure i need to check the timestamp values from the table valued parameter against the db before i can update the record


    Anonymous
    Monday, August 15, 2011 4:52 PM
  • Hi phbuser,

    According to your description, i have demonstrated the scenario as following statements:

    USE tempdb 
    GO
    
    --create a table
    CREATE TABLE dbo.TTimeStamp (
    	col1 char (10) NOT NULL ,
    	myTimeStamp timestamp NOT NULL 
    )
    GO
    
    Insert into TTimestamp (col1) values('hello')
    Insert into TTimestamp (col1) values('no')
    GO
    
    
    --create a PROCEDURE
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[sp_TimeStamp]
     @iTemp timestamp
    AS
    BEGIN
      SET NOCOUNT ON;
      
    SELECT CASE WHEN @iTemp=myTimeStamp THEN 1 ELSE 0 END FROM TTimestamp
    
    END ;
    
    GO
    
    --exec
    DECLARE @itemp timestamp
    SET @itemp=0x0000000000000924
    EXEC sp_TimeStamp @itemp
    


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    • Marked as answer by Stephanie Lv Monday, August 22, 2011 9:27 AM
    Tuesday, August 16, 2011 5:57 AM
  • Hi phbuser,

    Were you able to successfully pass the Tiimestamp through TVP by declaring it as varchar? I am facing an issue by passing it as byte[]. The error message is Cannot insert values into a Timestamp column. Also I wanted to know if the casting back to Timestamp in the SP retains the functionality of Timestamp?

    Thanks,

    Praveen

    Tuesday, December 06, 2011 1:26 PM
  • The OP stated that they are trying to pass a timestamp as a TVP column.   Your answer does not use a TVP to pass in the timestamp value.   Passing in a timstamp value is necessary if doing concurrency checking on each of the rows in the TVP.

    Wednesday, October 30, 2013 6:05 PM