SQL Table Variable Issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » SQL Table Variable Issue

SQL Table Variable Issue

Rate Topic

Display Mode

Topic Options

Author

Message

paul.kuczwara

Posted Tuesday, November 18, 2008 1:35 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2008 4:02 PM
Points: 18, Visits: 32

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

Post #604682

Garadin

Posted Tuesday, November 18, 2008 1:50 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

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. Wink

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

Post #604690

paul.kuczwara

Posted Tuesday, November 18, 2008 1:56 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2008 4:02 PM
Points: 18, Visits: 32

Thank you Seth,

That solved the issue.

Regards,
Paul

Post #604693

Jack Corbett

Posted Tuesday, November 18, 2008 2:08 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

UPDATE @EntityDateTimes
   SET EventID=(SELECT TOP 1 
                   EventID        
                FROM 
                   tkEntityHistory 
                WHERE 
                   EntityID=@EntityDateTimes.EntityID AND 
                   [DateTime]=@EntityDateTimes.[DateTime])

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #604697

Garadin

Posted Tuesday, November 18, 2008 2:09 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

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. Wink

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

Post #604699

Jack Corbett

Posted Tuesday, November 18, 2008 2:42 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Garadin (11/18/2008)

   	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.

Post #604718

paul.kuczwara

Posted Tuesday, November 18, 2008 2:53 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2008 4:02 PM
Points: 18, Visits: 32

Thank you Seth for the advice.

Regards,
Paul

Post #604723

Garadin

Posted Tuesday, November 18, 2008 3:15 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

Jack Corbett (11/18/2008)

Thanks Jack.

Seth Phelabaum

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

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

Post #604733

paul.kuczwara

Posted Tuesday, November 18, 2008 3:22 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 18, 2008 4:02 PM
Points: 18, Visits: 32

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 :D

Post #604739

Jeff Moden

Posted Tuesday, November 18, 2008 7:06 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910, Visits: 26,800

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #604792

« Prev Topic | Next Topic »

Permissions