Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Table Variable Issue Expand / Collapse
Author
Message
Posted Tuesday, November 18, 2008 1:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 18, 2008 1:50 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,519, Visits: 4,071
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #604690
Posted Tuesday, November 18, 2008 1:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 18, 2008 2:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 10,193, Visits: 13,121
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
Posted Tuesday, November 18, 2008 2:09 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,519, Visits: 4,071
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



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

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #604699
Posted Tuesday, November 18, 2008 2:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 10,193, Visits: 13,121
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



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.




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 #604718
Posted Tuesday, November 18, 2008 2:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 18, 2008 3:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 9:38 AM
Points: 1,519, Visits: 4,071
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.



Thanks Jack.


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

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #604733
Posted Tuesday, November 18, 2008 3:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 18, 2008 7:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:46 PM
Points: 35,218, Visits: 31,676
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #604792
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse