none
Timestamp column and output parameter RRS feed

  • Question

  • Hi,

     

    I'm currently experiencing an issue with a stored proc. I'm sending in a set of parameters to create a row in one of my tables, one of the parameters is an ID (Integer) type that is auto-generated as an "IDENTITY" column, another is a Timestamp column used to track multi-user updates. Both are OUTPUT parameters.

     

    The problem I'm having is that after an insert inside the proc, I can happily get the ID column value (using SCOPE_IDENTITY), however the timestamp column is always returned out as a sequence of empty bytes (in vb.net). executing this in a query analyzer window yields the same thing, a set of 8 small square characters, however an immediate "SELECT" from the table in question yields a proper value for the new timestamp field.

     

    Is there something I'm missing when executing this INSERT into a table ?...

     

    I have a similar scenario for updates, however the timestamp field is returned perfectly (altered) from that particular SP after the update has run.

     

    Both of these SP's would be operating under a transaction. I would expect them to either both fail to return the "post insert/update" timestamp value or both fail. I'm at a loss to understand why I'm getting the "altered" value from the update but NOT from the insert.

     

    Any ideas anybody ?... Please....

     

    Thanks.

    Monday, June 18, 2007 1:03 PM

Answers

  • Hi Paul,

     

    See this statement in the sp:

     

    > -- Get the changed and auto-generated data

    > SELECT @RecordModUser_ID = @RecordModUser_ID , @RecordModTS = @RecordModTS

    > FROM [GroupHierarchy]

    > WHERE ID = @ID

     

    Shouldn't it be:

     

    -- Get the changed and auto-generated data

    SELECT @RecordModUser_ID = RecordModUser_ID , @RecordModTS = RecordModTS

    FROM [GroupHierarchy]

    WHERE ID = @ID

     

     

    Same in both sps.

     

    AMB

     

    Monday, June 18, 2007 2:15 PM
    Moderator

All replies

  • Can we see the code?

     

    select @i = identity_column, @t = timestamp_column

    from my_table

    where identity_column = scope_identity()

     

     

    AMB

    Monday, June 18, 2007 1:17 PM
    Moderator
  • Sure,

     

    Here's the INSERT procedure....

     

    --START CODE SEGMENT--------------------------------

    CREATE PROCEDURE usp_GroupHierarchy_Create

    -- **** START Common Parameters to all procedures ****

    @Debug AS BIT = 0

    , @RowCount AS INTEGER OUTPUT

    , @RecordModUser_ID AS INTEGER OUTPUT

    , @RecordModTS AS TIMESTAMP OUTPUT

    , @ID AS INTEGER OUTPUT

    -- **** END Common Parameters to all procedures ****

    , @ParentGroupHierarchy_ID INT

    , @CustomerType_ID INT

    , @GroupName VARCHAR ( 255 )

    , @GroupLevel INT

    , @HasChildGroups BIT

    , @GroupHierarchyType_ID INT

    AS

    --------------------------------------------------

    -- Stored Procedure Pseudo Constant

    DECLARE @C_PROC_NAME AS VARCHAR ( 130 )

    SET @C_PROC_NAME = OBJECT_NAME(@@PROCID)

    -- Some pseudo constant values

    DECLARE @C_MULTI_USER_ERROR AS INTEGER

    DECLARE @C_OTHER_UNIQUE_KEY_ERROR AS INTEGER

    DECLARE @C_OTHER_UNKNOWN_ERROR AS INTEGER

    -- Set the pseudo constant values

    SET @C_MULTI_USER_ERROR = 50001

    SET @C_OTHER_UNIQUE_KEY_ERROR = 50002

    SET @C_OTHER_UNKNOWN_ERROR = 50003

    -- Local Variables

    DECLARE @ConflictName AS VARCHAR ( 200 )

    DECLARE @ErrorMessage AS VARCHAR ( 255 )

    DECLARE @Section AS VARCHAR ( 255 )

    --------------------------------------------------

    --------------------------------------------------

    -- Do the Insert work here

    INSERT INTO [GroupHierarchy] (

    [RecordModUser_ID ]

    , [ParentGroupHierarchy_ID]

    , [CustomerType_ID]

    , [GroupName]

    , [GroupLevel]

    , [HasChildGroups]

    , [GroupHierarchyType_ID]

    )

    VALUES (

    @RecordModUser_ID

    , @ParentGroupHierarchy_ID

    , @CustomerType_ID

    , @GroupName

    , @GroupLevel

    , @HasChildGroups

    , @GroupHierarchyType_ID

    )

    --------------------------------------------------

    --------------------------------------------------

    -- Get The Row Count (Before any other statements)

    SET @RowCount = @@RowCount

    --------------------------------------------------

    --------------------------------------------------

    -- Get The Most Recent ID Column (In this scope)

    SET @ID = SCOPE_IDENTITY()

    --------------------------------------------------

    --------------------------------------------------

    -- Get the changed and auto-generated data

    SELECT @RecordModUser_ID = @RecordModUser_ID ,

    @RecordModTS = @RecordModTS

    FROM [GroupHierarchy]

    WHERE ID = @ID

    --------------------------------------------------

    --------------------------------------------------

    -- Do We Have An Error

    IF @@ERROR <> 0 BEGIN

    GOTO LBL_ErrorHandler

    END

    --------------------------------------------------

    --------------------------------------------------

    -- All Done

    RETURN(0)

    --------------------------------------------------

    --------------------------------------------------

    -- Error Handlers

    LBL_OtherUniqueKeyError:

    -- Do we need some more debug information ?...

    IF ( @Debug = 1 ) PRINT @Section

    -- Raise error to the user

    RAISERROR ( @C_OTHER_UNIQUE_KEY_ERROR , 16 /*severity*/ , 1 /*state*/ , @C_PROC_NAME , N'<ConstraintName>' )

    -- Return error indicator

    RETURN ( 1 )

    --------------------------------------------------

    LBL_MultiUserError:

    -- Do we need some more debug information ?...

    IF ( @Debug = 1 ) PRINT @Section

    -- Raise error to the user

    RAISERROR ( @C_MULTI_USER_ERROR , 16 /*severity*/ , 1 /*state*/ , @C_PROC_NAME , @ConflictName )

    -- Return error indicator

    RETURN ( 1 )

    --------------------------------------------------

    LBL_ErrorHandler:

    -- Do we need some more debug information ?...

    IF ( @Debug = 1 ) PRINT @Section

    -- Raise error to the user

    RAISERROR ( @C_OTHER_UNKNOWN_ERROR , 16 /*severity*/ , 1 /*state*/ , @Section , @C_PROC_NAME )

    -- Return error indicator

    RETURN ( 1 )

    -- END CODE SEGMENT --------------------------------

     

    ... and here's the UPDATE one....

    --START CODE SEGMENT--------------------------------

     

    CREATE Procedure usp_GroupHierarchy_Update

    -- **** START Common Parameters to all procedures ****

    @Debug AS BIT = 0

    , @RowCount AS INTEGER OUTPUT

    , @RecordModUser_ID AS INTEGER OUTPUT

    , @RecordModTS AS TIMESTAMP OUTPUT

    , @ID AS INTEGER OUTPUT

    -- **** END Common Parameters to all procedures ****

    , @ParentGroupHierarchy_ID INT

    , @CustomerType_ID INT

    , @GroupName VARCHAR ( 255 )

    , @GroupLevel INT

    , @HasChildGroups BIT

    , @GroupHierarchyType_ID INT

    AS

    --------------------------------------------------

    -- Stored Procedure Pseudo Constant

    DECLARE @C_PROC_NAME AS VARCHAR ( 130 )

    SET @C_PROC_NAME = OBJECT_NAME(@@PROCID)

    -- Some pseudo constant values

    DECLARE @C_MULTI_USER_ERROR AS INTEGER

    DECLARE @C_OTHER_UNIQUE_KEY_ERROR AS INTEGER

    DECLARE @C_OTHER_UNKNOWN_ERROR AS INTEGER

    -- Set the pseudo constant values

    SET @C_MULTI_USER_ERROR = 50001

    SET @C_OTHER_UNIQUE_KEY_ERROR = 50002

    SET @C_OTHER_UNKNOWN_ERROR = 50003

    -- Local Variables

    DECLARE @ConflictName AS VARCHAR ( 200 )

    DECLARE @ErrorMessage AS VARCHAR ( 255 )

    DECLARE @Section AS VARCHAR ( 255 )

    --------------------------------------------------

    --------------------------------------------------

    -- User Conflict Check

    IF EXISTS ( SELECT 'x' FROM [GroupHierarchy] WHERE [ID] = @ID AND [RecordModTS] <> @RecordModTS ) BEGIN

    GOTO LBL_MultiUserError

    END

    --------------------------------------------------

    --------------------------------------------------

    -- Do the Insert work here

    UPDATE [GroupHierarchy]

    SET [RecordModUser_ID] = @RecordModUser_ID

    , [ParentGroupHierarchy_ID] = @ParentGroupHierarchy_ID

    , [CustomerType_ID] = @CustomerType_ID

    , [GroupName] = @GroupName

    , [GroupLevel] = @GroupLevel

    , [HasChildGroups] = @HasChildGroups

    , [GroupHierarchyType_ID] = @GroupHierarchyType_ID

    WHERE 1 =1

    AND [ID] = @ID

    --------------------------------------------------

    --------------------------------------------------

    -- Get The Row Count

    SET @RowCount = @@RowCount

    --------------------------------------------------

    --------------------------------------------------

    -- Get the changed data

    SELECT @RecordModUser_ID = @RecordModUser_ID ,

    @RecordModTS = @RecordModTS

    FROM [GroupHierarchyType]

    WHERE ID = @ID

    --------------------------------------------------

    --------------------------------------------------

    -- If we didn't update a row then it may have been deleted

    IF ( @RowCount <> 1 ) BEGIN

    GOTO LBL_MultiUserError

    END

    --------------------------------------------------

    --------------------------------------------------

    -- Do We Have An Error

    IF @@ERROR <> 0 BEGIN

    GOTO LBL_ErrorHandler

    END

    --------------------------------------------------

    --------------------------------------------------

    -- All Done

    RETURN(0)

    --------------------------------------------------

    --------------------------------------------------

    -- Error Handlers

    LBL_OtherUniqueKeyError:

    -- Do we need some more debug information ?...

    IF ( @Debug = 1 ) PRINT @Section

    -- Raise error to the user

    RAISERROR ( @C_OTHER_UNIQUE_KEY_ERROR , 16 /*severity*/ , 1 /*state*/ , @C_PROC_NAME , N'<ConstraintName>' )

    -- Return error indicator

    RETURN ( 1 )

    --------------------------------------------------

    LBL_MultiUserError:

    -- Do we need some more debug information ?...

    IF ( @Debug = 1 ) PRINT @Section

    -- Raise error to the user

    RAISERROR ( @C_MULTI_USER_ERROR , 16 /*severity*/ , 1 /*state*/ , @C_PROC_NAME , @ConflictName )

    -- Return error indicator

    RETURN ( 1 )

    --------------------------------------------------

    LBL_ErrorHandler:

    -- Do we need some more debug information ?...

    IF ( @Debug = 1 ) PRINT @Section

    -- Raise error to the user

    RAISERROR ( @C_OTHER_UNKNOWN_ERROR , 16 /*severity*/ , 1 /*state*/ , @Section , @C_PROC_NAME )

    -- Return error indicator

    RETURN ( 1 )

    -- END CODE SEGMENT --------------------------------

     

    ... and finally to help you along, a "CREATE TABLE" script taken from the SQL server generated version of my table (SQL 2005)

     

    --START CODE SEGMENT--------------------------------

     

    USE [TestDB]

    GO

    /****** Object: Table [dbo].[GroupHierarchy] Script Date: 06/18/2007 15:03:39 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[GroupHierarchy](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [RecordModUser_ID] [int] NULL,

    [RecordModTS] [timestamp] NOT NULL,

    [ParentGroupHierarchy_ID] [int] NULL,

    [CustomerType_ID] [int] NULL CONSTRAINT [DF__GroupHier__Custo__60A75C0F] DEFAULT ((4)),

    [GroupName] [varchar](255) COLLATE Latin1_General_CI_AS NULL,

    [GroupLevel] [int] NULL,

    [HasChildGroups] [bit] NULL CONSTRAINT [DF__GroupHier__HasCh__619B8048] DEFAULT ((0)),

    [GroupHierarchyType_ID] [int] NULL,

    CONSTRAINT [PK_GroupHierarchy] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    -- END CODE SEGMENT --------------------------------

    Monday, June 18, 2007 2:05 PM
  • Hi Paul,

     

    See this statement in the sp:

     

    > -- Get the changed and auto-generated data

    > SELECT @RecordModUser_ID = @RecordModUser_ID , @RecordModTS = @RecordModTS

    > FROM [GroupHierarchy]

    > WHERE ID = @ID

     

    Shouldn't it be:

     

    -- Get the changed and auto-generated data

    SELECT @RecordModUser_ID = RecordModUser_ID , @RecordModTS = RecordModTS

    FROM [GroupHierarchy]

    WHERE ID = @ID

     

     

    Same in both sps.

     

    AMB

     

    Monday, June 18, 2007 2:15 PM
    Moderator
  • Hi....

     

    Please delete this thread......................................................................................

     

    You know, when you stare at something for so long you can't see the wood for the trees....

     

    Ta..

    Monday, June 18, 2007 2:27 PM
  • Hi Paul,

     

    Take it easy, most of us (I do not want to say ALL) have been there.

     

     

    AMB

    Monday, June 18, 2007 2:56 PM
    Moderator
  • I think that it is fair to say 'ALL'!

     

    And besides, a little public egg on our face is also a 're-centering' event.

    Monday, June 18, 2007 6:24 PM
    Moderator