November 18, 2008 at 1:35 pm
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
November 18, 2008 at 1:50 pm
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
November 18, 2008 at 1:56 pm
Thank you Seth,
That solved the issue.
Regards,
Paul
November 18, 2008 at 2:08 pm
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]
Jack Corbett
Consultant - Straight Path Solutions
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
November 18, 2008 at 2:09 pm
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:
November 18, 2008 at 2:42 pm
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 @EntityDateTimesSET 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.
Jack Corbett
Consultant - Straight Path Solutions
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
November 18, 2008 at 2:53 pm
Thank you Seth for the advice.
Regards,
Paul
November 18, 2008 at 3:15 pm
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.
November 18, 2008 at 3:22 pm
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 😀
November 18, 2008 at 7:06 pm
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
Change is inevitable... Change for the better is not.
May 27, 2013 at 2:38 pm
I want to clear the records and also need to reset the identity.. Truncate table does not help... Please suggest for other alternatives...
May 28, 2013 at 7:17 am
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.
Jack Corbett
Consultant - Straight Path Solutions
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply