November 18, 2008 at 9:48 am
Good Morning,
I am relatively new at using SQL and stored procedures. I am trying to tune our SP's by using "Tables Variables" for the temp tables and I am having difficulty with the following areas. Can someone advise me to what I am doing wrong?
Regards,
Paul
Before the changes I made these two table variables were temp tables. @tmp_subjects and @tmp_objects like this #tmp_subjects and #tmp_objects. These stored procedures work when I use temp tables. Changing these to table variables I get the following errors:
Msg 137, Level 15, State 2, Procedure tkspCreateRelation2, Line 57
Must declare the scalar variable "@tmp_subjects".
and
Msg 137, Level 15, State 2, Procedure tkspCreateRelation2, Line 67
Must declare the scalar variable "@tmp_objects".
They seem to go out of scope, is there a solution, or do I need to use temp tables? Both table variables are declared and initialed as follows:
DECLARE @tmp_subjects TABLE (ObjectID uniqueidentifier)
INSERT INTO @tmp_subjects SELECT EntityID FROM tkSecurityContext (NOLOCK) WHERE SecurityContextID=@SubjectSCID
IF (@@ROWCOUNT>1)
SET @MultipleSubject=1
DECLARE @tmp_objects TABLE (ObjectID uniqueidentifier)
INSERT INTO @tmp_objects SELECT EntityID FROM tkSecurityContext (NOLOCK) WHERE SecurityContextID=@ObjectSCID
IF (@@ROWCOUNT>1)
SET @MultipleObject=1
--check permissions
IF (@NoPermCheck=0)
BEGIN
IF EXISTS(SELECT ObjectID FROM @tmp_subjects
INNER JOIN tkObjects o ON @tmp_subjects.ObjectID=o.ObjectID
LEFT OUTER JOIN tkEntityPermissions ep (NOLOCK)
ON (o.ObjectID = ep.EntityID
AND ep.PersonID IN
(SELECT EntityID FROM tkSecurityContext (NOLOCK) WHERE SecurityContextID=@SecurityContextID))
WHERE ((ep.AccessLevel IS NULL) AND (o.DefaultAccessLevel & 64)=0)
OR ((ep.AccessLevel IS NOT NULL) AND (ep.AccessLevel & 64)=0)
)
RETURN 1
IF EXISTS(SELECT ObjectID FROM @tmp_objects
INNER JOIN tkObjects o ON @tmp_objects.ObjectID=o.ObjectID
LEFT OUTER JOIN tkEntityPermissions ep (NOLOCK)
ON (o.ObjectID = ep.EntityID
AND ep.PersonID IN
(SELECT EntityID FROM tkSecurityContext (NOLOCK) WHERE SecurityContextID=@SecurityContextID))
WHERE ((ep.AccessLevel IS NULL) AND (o.DefaultAccessLevel & 64)=0)
OR ((ep.AccessLevel IS NOT NULL) AND (ep.AccessLevel & 64)=0)
)
RETURN 1
END
November 18, 2008 at 9:58 am
is this all the code?
I have a feeling that something is missing here?
you code section isn't 57 lines long?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 18, 2008 at 9:59 am
You showed us only part of the code. You need to show us all the relent code. Since you mentioned in your message that you worked with stored procedures, I guess that you declared the table variable in the outer stored procedure and then called the inner stored procedure and tried to work with the table variable (just like you can do with temporary tables). If I’m correct, then there is your problem. Table variable is a variable. Just like the inner procedure doesn’t know nor recognize other variable that are declared in the outer stored procedure (for example int or varchar variables), it also doesn’t recognize table variables. If I’m wrong and you are not using 2 stored procedures, or you have a different problem, pleas post all relevant code and not just part of the code.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
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/
November 18, 2008 at 10:01 am
May I also just add that, changing from a temp table to a table variable may not give you a performance boost...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 18, 2008 at 10:03 am
Hi Chris,
Here is the entire code. But what I gave you is the revelent section casuing the issues.
Regards,
Paul
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/****** Object: Stored Procedure dbo.tkspCreateRelation2 Script Date: 7/8/2003 11:55:42 AM ******/
/*
Version 0, month dd, yyyy
Version 1, Noverber 12, 2008 -- Modify the temp tables (#tmp) with Table variables (@tmp) -- Paul Kuczwara
*/
/****** Object: Stored Procedure dbo.tkspCreateRelation2 Script Date: 1/6/2003 5:09:39 PM ******/
/****** Object: Stored Procedure dbo.tkspCreateRelation2 Script Date: 1/6/2003 5:09:39 PM ******/
ALTER PROCEDURE [dbo].[tkspCreateRelation2]
@ModifierID uniqueidentifier,
@SubjectSCID uniqueidentifier,
@ObjectSCID uniqueidentifier,
@Description nvarchar(255)=NULL,
@AddToHistory bit=0,
@SecurityContextID uniqueidentifier,
@user-id uniqueidentifier,
@DateTime datetime,
@NoPermCheck bit=0,
@ResultRelationID uniqueidentifier OUTPUT
AS
SET NOCOUNT ON
DECLARE @SubjectTypeID uniqueidentifier
DECLARE @ObjectTypeID uniqueidentifier
DECLARE @ResultCode int
DECLARE @ModSingularText nvarchar(255)
DECLARE @ModReverseSingularText nvarchar(255)
DECLARE @ObjectID uniqueidentifier
DECLARE @MultipleSubject bit
DECLARE @MultipleObject bit
DECLARE @SubjFormalName nvarchar(1000)
DECLARE @ObjFormalName nvarchar(1000)
DECLARE @FormalName nvarchar(1000)
--get temp table with subjects and direct objects
DECLARE @tmp_subjects TABLE (ObjectID uniqueidentifier)
INSERT INTO @tmp_subjects SELECT EntityID FROM tkSecurityContext (NOLOCK) WHERE SecurityContextID=@SubjectSCID
IF (@@ROWCOUNT>1)
SET @MultipleSubject=1
DECLARE @tmp_objects TABLE (ObjectID uniqueidentifier)
INSERT INTO @tmp_objects SELECT EntityID FROM tkSecurityContext (NOLOCK) WHERE SecurityContextID=@ObjectSCID
IF (@@ROWCOUNT>1)
SET @MultipleObject=1
--check permissions
IF (@NoPermCheck=0)
BEGIN
IF EXISTS(SELECT ObjectID FROM @tmp_subjects
INNER JOIN tkObjects o ON @tmp_subjects.ObjectID=o.ObjectID
LEFT OUTER JOIN tkEntityPermissions ep (NOLOCK)
ON (o.ObjectID = ep.EntityID
AND ep.PersonID IN
(SELECT EntityID FROM tkSecurityContext (NOLOCK) WHERE SecurityContextID=@SecurityContextID))
WHERE ((ep.AccessLevel IS NULL) AND (o.DefaultAccessLevel & 64)=0)
OR ((ep.AccessLevel IS NOT NULL) AND (ep.AccessLevel & 64)=0)
)
RETURN 1
IF EXISTS(SELECT ObjectID FROM @tmp_objects
INNER JOIN tkObjects o ON @tmp_objects.ObjectID=o.ObjectID
LEFT OUTER JOIN tkEntityPermissions ep (NOLOCK)
ON (o.ObjectID = ep.EntityID
AND ep.PersonID IN
(SELECT EntityID FROM tkSecurityContext (NOLOCK) WHERE SecurityContextID=@SecurityContextID))
WHERE ((ep.AccessLevel IS NULL) AND (o.DefaultAccessLevel & 64)=0)
OR ((ep.AccessLevel IS NOT NULL) AND (ep.AccessLevel & 64)=0)
)
RETURN 1
END
IF (@ResultRelationID IS NULL)
SET @ResultRelationID = newid()
INSERT INTO tkRelations (RelationID, ModifierID, Description)
SELECT @ResultRelationID, @ModifierID, @Description
INSERT INTO tkRelationObjects (RelationObjectID, RelationID, ObjectID, RelPos)
SELECT newid(), @ResultRelationID, ObjectID, 1 FROM @tmp_subjects
INSERT INTO tkRelationObjects (RelationObjectID, RelationID, ObjectID, RelPos)
SELECT newid(), @ResultRelationID, ObjectID, 2 FROM @tmp_objects
IF (@AddToHistory=1)
BEGIN
SELECT @ModSingularText=SingularText, @ModReverseSingularText=ReverseSingularText
FROM tkRelationModifiers WHERE ModifierID=@ModifierID
IF (@MultipleObject=1)
SET @ObjFormalName='multiple objects'
ELSE
SELECT @ObjFormalName=ot.SingularName + ' ''' + o.ObjectName + ''''
FROM tkObjects o INNER JOIN tkObjectTypes ot ON o.ObjectTypeID=ot.ObjectTypeID
WHERE o.ObjectID IN (SELECT TOP 1 ObjectID FROM @tmp_objects)
IF (@MultipleSubject=1)
SET @SubjFormalName='multiple objects'
ELSE
SELECT @SubjFormalName=ot.SingularName + ' ''' + o.ObjectName + ''''
FROM tkObjects o INNER JOIN tkObjectTypes ot ON o.ObjectTypeID=ot.ObjectTypeID
WHERE o.ObjectID IN (SELECT TOP 1 ObjectID FROM @tmp_subjects)
WHILE EXISTS(SELECT ObjectID FROM @tmp_subjects)
BEGIN
SELECT @ObjectID=ObjectID FROM @tmp_subjects
SET @FormalName=@ModSingularText+' '+@ObjFormalName
EXECUTE tkspAddToHistory
@EntityID=@ObjectID,
@Description=@FormalName,
@DateTime=@DateTime,
@EventType='Create Relation',
@user-id=@UserID
DELETE FROM @tmp_subjects WHERE ObjectID=@ObjectID
END
WHILE EXISTS(SELECT ObjectID FROM @tmp_objects)
BEGIN
SELECT @ObjectID=ObjectID FROM @tmp_objects
SET @FormalName=@ModReverseSingularText+' '+@SubjFormalName
EXECUTE tkspAddToHistory
@EntityID=@ObjectID,
@Description=@FormalName,
@DateTime=@DateTime,
@EventType='Create Relation',
@user-id=@UserID
DELETE FROM @tmp_objects WHERE ObjectID=@ObjectID
END
END
RETURN 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
November 18, 2008 at 10:06 am
hi again,
OK you need to alias your tables you can't fully qualify colums in a table variable.
You code should be like this.
IF (@NoPermCheck=0)
BEGIN
IF EXISTS(SELECT ObjectID FROM @tmp_subjects t1
INNER JOIN tkObjects o ON t1.ObjectID=o.ObjectID
LEFT OUTER JOIN tkEntityPermissions ep (NOLOCK)
ON (o.ObjectID = ep.EntityID
AND ep.PersonID IN
(SELECT EntityID FROM tkSecurityContext (NOLOCK) WHERE SecurityContextID=@SecurityContextID))
WHERE ((ep.AccessLevel IS NULL) AND (o.DefaultAccessLevel & 64)=0)
OR ((ep.AccessLevel IS NOT NULL) AND (ep.AccessLevel & 64)=0)
)
RETURN 1
IF EXISTS(SELECT ObjectID FROM @tmp_objects t1
INNER JOIN tkObjects o ON t1.ObjectID=o.ObjectID
LEFT OUTER JOIN tkEntityPermissions ep (NOLOCK)
ON (o.ObjectID = ep.EntityID
AND ep.PersonID IN
(SELECT EntityID FROM tkSecurityContext (NOLOCK) WHERE SecurityContextID=@SecurityContextID))
WHERE ((ep.AccessLevel IS NULL) AND (o.DefaultAccessLevel & 64)=0)
OR ((ep.AccessLevel IS NOT NULL) AND (ep.AccessLevel & 64)=0)
)
RETURN 1
END
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 18, 2008 at 10:14 am
Hi Chris,
I already tried to alias the Table Variables. When I do I am getting an ambigious referrance to the ObjectID in both tables. Seems that the tables have gone out of scope. Is there another possible issue that I am not seeing?
Msg 209, Level 16, State 1, Procedure tkspCreateRelation2, Line 56
Ambiguous column name 'ObjectID'.
Msg 209, Level 16, State 1, Procedure tkspCreateRelation2, Line 66
Ambiguous column name 'ObjectID'.
Regards,
Paul
November 18, 2008 at 10:16 am
ok but are you using the alias to reference the column ?
Eg.
SELECT
...
t1.ObjectId
FROM @mytable t1
INNER JOIN blah t2 ON t1.ObjectId = t2.objectID
...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 18, 2008 at 10:21 am
Hi Chris,
Yes, I am using the alias to referance the column in question .
Regards,
Paul
IF EXISTS(SELECT ObjectID FROM @tmp_subjects t1
INNER JOIN tkObjects o ON t1.ObjectID=o.ObjectID
IF EXISTS(SELECT ObjectID FROM @tmp_objects t2
INNER JOIN tkObjects o ON t2.ObjectID=o.ObjectID
November 18, 2008 at 10:27 am
yes but you need to alias in the select part of the query as well otherwise sql doesn't know which table that columns is coming from...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 18, 2008 at 10:34 am
Hi Chris,
This is what I have done.
IF EXISTS(SELECT ObjectID FROM @tmp_subjects t1
INNER JOIN tkObjects o ON t1.ObjectID=o.ObjectID
and
IF EXISTS(SELECT ObjectID FROM @tmp_objects t2
INNER JOIN tkObjects o ON t2.ObjectID=o.ObjectID
Doing this I still am getting the ambiguios referrance error. Could this a scope issue, from the table variable declaration and the using of it?
Regards,
Paul
November 18, 2008 at 10:50 am
Right, and you need to alias the Select part of the query, as Chris keeps telling you =).
IF EXISTS(SELECT T1.ObjectID FROM @tmp_subjects t1
INNER JOIN tkObjects o ON t1.ObjectID=o.ObjectID
If you don't tell it which objectID you want to select, it's ambiguous.
November 18, 2008 at 11:02 am
Thanks Chris and Garadin,
That worked,
As I said, I am relatively new at using SQL and stored procedures.
Thanks Guys,
I appreciate it!! 🙂
Regards,
Paul
November 18, 2008 at 9:22 pm
My recommendation is to leave the temp tables as they are instad of converting them to table variables... Read Q3/A3 and Q4/A4 in the following to see why...
http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k
Yeah... I know it's a 2k listing... still applies to 2k5.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2008 at 7:32 am
Chris,
I couldn't bare the pain any longer reading your suggestions.
Let me give hime the answer:
SELECT T1.ObjectID FROM....
SELECT T2.ObjectID FROM ....
see below:
IF EXISTS(SELECT T1.ObjectID FROM @tmp_subjects t1
INNER JOIN tkObjects o ON t1.ObjectID=o.ObjectID
IF EXISTS(SELECT T2.ObjectID FROM @tmp_objects t2
INNER JOIN tkObjects o ON t2.ObjectID=o.ObjectID
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply