SQL Table Variable Issue

  • Good Afternoon All,

    I have defined a new Table Variable and initialized it. When I try to referance a column from the Table variable I am getting an error stating that the "Must declare the scalar variable "@EntityDateTimes"." I tried to alias the table but I am not sure where to put the alias.

    Regards,

    Paul

    Here is the stored Procedure:

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    ALTER PROCEDURE [dbo].[tkspObjectRevHistory]

    /*

    Author: JSW

    Version 0, July 3, 2001

    Version 1, Noverber 13, 2008 -- Modify the temp tables (#tmp) with Table variables (@tmp) -- Paul Kuczwara

    This procedure retrieves revision history of an object and returns

    a recordset containing one row per revision. The information in

    each row is the latest information about that revision.

    This procedure has the following input parameters

    */

    @ObjectID uniqueidentifier,

    @forward bit = 0

    AS

    SET NOCOUNT ON

    DECLARE @ResultCode int

    DECLARE @ObjectTypeID uniqueidentifier

    DECLARE @ACursor1 cursor

    DECLARE @ACursor2 cursor

    DECLARE @RelObjectID uniqueidentifier

    DECLARE @Found bit

    --get the object type

    SELECT @ObjectTypeID=ObjectTypeID FROM tkObjects WHERE ObjectID=@ObjectID

    IF (@ObjectTypeID IS NULL)

    RETURN 1

    --create temporary table to collect object IDs

    DECLARE @EntityHistory TABLE (ObjectID uniqueidentifier)

    INSERT INTO @EntityHistory (ObjectID) SELECT @ObjectID

    IF (@Forward=0)

    BEGIN

    --collect all previous versions of the object

    SET @RelObjectID=@ObjectID

    SET @Found=1

    WHILE (@Found=1)

    BEGIN

    EXECUTE @ResultCode = tkspRelatedObjectsHomogeneousCursor

    @ObjectID=@RelObjectID,

    @RelPos=1, -- assume ObjectID is the later version

    @ModifierName='$Object-PreviousVersion',

    @ObjectTypeID=@ObjectTypeID,

    @ObjectStatusSet=1,

    @ObjectStatusReset=1,--get objects either active or inactive

    @ResultCursor = @ACursor1 OUTPUT

    IF (@ResultCode<>0)

    RETURN 2

    FETCH NEXT FROM @ACursor1 INTO @RelObjectID

    IF (@@FETCH_STATUS=0)

    INSERT INTO @EntityHistory (ObjectID) SELECT @RelObjectID

    ELSE

    SET @Found=0

    CLOSE @ACursor1

    DEALLOCATE @ACursor1

    END

    END

    --collect all later versions of the object

    SET @RelObjectID=@ObjectID

    SET @Found=1

    WHILE (@Found=1)

    BEGIN

    EXECUTE @ResultCode = tkspRelatedObjectsHomogeneousCursor

    @ObjectID=@RelObjectID,

    @RelPos=2, -- assume ObjectID is the previous version

    @ModifierName='$Object-PreviousVersion',

    @ObjectTypeID=@ObjectTypeID,

    @ObjectStatusSet=1,

    @ObjectStatusReset=1,--get objects either active or inactive

    @ResultCursor = @ACursor2 OUTPUT

    IF (@ResultCode<>0)

    RETURN 2

    FETCH NEXT FROM @ACursor2 INTO @RelObjectID

    IF (@@FETCH_STATUS=0)

    INSERT INTO @EntityHistory (ObjectID) SELECT @RelObjectID

    ELSE

    SET @Found=0

    CLOSE @ACursor2

    DEALLOCATE @ACursor2

    END

    DECLARE @EntityDateTimes TABLE (EntityID uniqueidentifier, [DateTime] datetime, EventID uniqueidentifier)

    INSERT INTO @EntityDateTimes (EntityID, [DateTime])

    SELECT EntityID, MIN([DateTime]) as EventDateTime FROM tkEntityHistory

    WHERE EntityID IN (SELECT EH.ObjectID FROM @EntityHistory EH)

    AND EventType IN ('Create','Check Out','Set Data','Check In')

    GROUP BY EntityID

    UPDATE @EntityDateTimes

    SET EventID=(SELECT TOP 1 EventID

    FROM tkEntityHistory )

    WHERE EntityID=@EntityDateTimes.EntityID

    AND [DateTime]=@EntityDateTimes.[DateTime])

    SELECT o.ObjectID, o.ObjectName, o.HistoryRev, o.Status, ehdt.[DateTime], eh.Description,

    eh.EventType, eh.UserID, o.Version, o.VersionDate, o.Description as ObjectDescription

    FROM @EntityDateTimes as ehdt INNER JOIN tkObjects as o ON ehdt.EntityID=o.ObjectID

    INNER JOIN tkEntityHistory as eh ON ehdt.EventID=eh.EventID

    ORDER BY o.HistoryRev DESC

    RETURN 0

  • Paul, please keep questions on one issue in one thread in the future. The issue is still the aliasing. When you get that error in Query Analyzer or SMSS, double click it. It will bring you right to Line 102 where you have:

    UPDATE @EntityDateTimes

    SET EventID=(SELECT TOP 1 EventID

    FROM tkEntityHistory )

    WHERE EntityID=@EntityDateTimes.EntityID

    AND [DateTime]=@EntityDateTimes.[DateTime])

    You cannot use Fully qualified names with Table variables.

    This needs to be:

    UPDATE @EntityDateTimes

    SET EventID=(SELECT TOP 1 EventID

    FROM tkEntityHistory

    WHERE EntityID=E.EntityID

    AND [DateTime]=E.[DateTime])

    FROM @EntityDateTimes E

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Thank you Seth,

    That solved the issue.

    Regards,

    Paul

  • Your problem is in the bolded section.

    UPDATE @EntityDateTimes

    SET EventID=(SELECT TOP 1 EventID

    FROM tkEntityHistory )

    WHERE EntityID=@EntityDateTimes.EntityID

    AND [DateTime]=@EntityDateTimes.[DateTime])

    Because @EntityDateTimes is not in the FROM clause the Query Processor doesn't know what you mean by @EntityDateTimes.EntityID.

    What are you trying to accomplish in this section of code? I thinky you may want the WHERE clause to be part of the subquery, then you would need to move the closing parenthesis to the end of the WHERE clause, and on closer inspection it looks like you have an extra parenthesis.

    You might try this

    [font="Courier New"]UPDATE @EntityDateTimes

       SET EventID=(SELECT TOP 1

                       EventID        

                    FROM

                       tkEntityHistory

                    WHERE

                       EntityID=@EntityDateTimes.EntityID AND

                       [DateTime]=@EntityDateTimes.[DateTime]) [/font]

  • Glad we could help. One other word of caution. You are selecting the Top 1 in that update clause, but you aren't ordering by anything. This could lead to unexpected/inconsistent results if you routinely have more than one EventID for each EntityID and DateTime. If you are sure that you DON'T ever have more than 1 EventID for any given EntityID or Datetime, then your query can be simplified to eliminate the subquery as so:

    UPDATE @EntityDateTimes

    SET EventID= EH.EventID

    FROM @EntityDateTimes E

    INNER JOIN tkEntityHistory EH ON E.EntityID=EH.EntityID AND E.[DateTime]=EH.[DateTime]

    I'll leave it to someone else to gripe about the cursors :hehe:

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Garadin (11/18/2008)


    Glad we could help. One other word of caution. You are selecting the Top 1 in that update clause, but you aren't ordering by anything. This could lead to unexpected/inconsistent results if you routinely have more than one EventID for each EntityID and DateTime. If you are sure that you DON'T ever have more than 1 EventID for any given EntityID or Datetime, then your query can be simplified to eliminate the subquery as so:

    UPDATE @EntityDateTimes

    SET EventID= EH.EventID

    FROM @EntityDateTimes E

    INNER JOIN tkEntityHistory EH ON E.EntityID=EH.EntityID AND E.[DateTime]=EH.[DateTime]

    I'll leave it to someone else to gripe about the cursors :hehe:

    Man, I hate it when someone else not only beats me with an answer, but has a better one 😉

    Nice job Seth.

    I wasn't going to complain about the cursor either. I just concentrated on the OP's question.

  • Thank you Seth for the advice.

    Regards,

    Paul

  • Jack Corbett (11/18/2008)


    Man, I hate it when someone else not only beats me with an answer, but has a better one 😉

    Nice job Seth.

    I wasn't going to complain about the cursor either. I just concentrated on the OP's question.

    :w00t: Thanks Jack.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Thank you to all who gave a hand today. I am sure I'll be back again, as I am working through a dozen or so stored procedures.

    Regards,

    Paul 😀

  • If they're all like the one you posted with a couple of cursors calling another sproc that probably has another cursor in it, you could be in for some real performance problems.

    What does tkspRelatedObjectsHomogeneousCursor look like?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I want to clear the records and also need to reset the identity.. Truncate table does not help... Please suggest for other alternatives...

  • Santosh Bala (5/27/2013)


    I want to clear the records and also need to reset the identity.. Truncate table does not help... Please suggest for other alternatives...

    Santosh,

    This has nothing to do with the original question on this thread. Please start a new Topic with your question.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply