Table Variable in SP?

Question

Table Variable in SP?

Karma-343206

SSCrazy

Points: 2104
More actions
June 27, 2006 at 8:42 am

#114150

Hi,
I'm trying out the table variable for the first time in 2000 and am struggling to get this SP to work. At the moment when i use the syntax check it comes back with
"Error 207 invalid column name TBL_LINK_GROUP_OBJECTS_OBJECTID invalid column name TBL_LINK_GROUP_OBJECTS_OBJECTID"
Could anyone advise me what might be causing this?
Thanks
K

CREATE PROCEDURE dbo.TestThisSproc AS
DECLARE @cmbo_groups nvarchar(50)
DECLARE @Temp1 TABLE ([TBL_OBJECTS_OBJECTID] int , [TBL_OBJECTS_OBJECTREALNAME] nvarchar(255), [TBL_OBJECTS_OBJECTNAME] nvarchar(255), [TBL_LINK_GROUP_OBJECTS_OBJECTID] int )
DECLARE @Temp2 TABLE ([TBL_OBJECTS_OBJECTID] int , [TBL_OBJECTS_OBJECTREALNAME] nvarchar(255), [TBL_OBJECTS_OBJECTNAME] nvarchar(255), [TBL_LINK_GROUP_OBJECT_GROUPID] int, [TBL_LINK_GROUP_OBJECT_OBJECTID] int)

SET              NOCOUNT ON
SET              ANSI_NULLS OFF
SET              ANSI_WARNINGS OFF
INSERT INTO
@Temp1 (TBL_OBJECTS_OBJECTID, TBL_OBJECTS_OBJECTREALNAME, TBL_OBJECTS_OBJECTNAME, TBL_LINK_GROUP_OBJECTS_OBJECTID)
SELECT
dbo.TBL_OBJECTS.OBJECTID, dbo.TBL_OBJECTS.OBJECTREALNAME, dbo.TBL_OBJECTS.OBJECTNAME, dbo.TBL_LINK_GROUP_OBJECT.OBJECTID
FROM
dbo.TBL_OBJECTS LEFT OUTER JOIN
dbo.TBL_LINK_GROUP_OBJECT
ON
dbo.TBL_OBJECTS.OBJECTID = TBL_LINK_GROUP_OBJECT_OBJECTID
GROUP BY dbo.TBL_OBJECTS.OBJECTID, dbo.TBL_OBJECTS.OBJECTREALNAME, dbo.TBL_OBJECTS.OBJECTNAME, TBL_LINK_GROUP_OBJECT_OBJECTID
INSERT INTO
@Temp2 (TBL_OBJECTS_OBJECTID, TBL_OBJECTS_OBJECTREALNAME, TBL_OBJECTS_OBJECTNAME , TBL_LINK_GROUP_OBJECT_GROUPID , TBL_LINK_GROUP_OBJECT_OBJECTID)
SELECT
dbo.TBL_OBJECTS.OBJECTID, dbo.TBL_OBJECTS.OBJECTREALNAME, dbo.TBL_OBJECTS.OBJECTNAME, dbo.TBL_LINK_GROUP_OBJECT.GROUPID, dbo.TBL_LINK_GROUP_OBJECT.OBJECTID
FROM
dbo.TBL_OBJECTS LEFT OUTER JOIN
dbo.TBL_LINK_GROUP_OBJECT
ON
dbo.TBL_OBJECTS.OBJECTID = dbo.TBL_LINK_GROUP_OBJECT.OBJECTID
WHERE
dbo.TBL_LINK_GROUP_OBJECT.GROUPID = @cmbo_groups
SELECT
*
FROM
@Temp1 as T1 LEFT JOIN
@Temp2 as T2
ON
T1.TBL_OBJECTS_OBJECTID = T2.TBL_OBJECTS_OBJECTID
WHERE
(T2.TBL_OBJECTS_OBJECTID) IS NULL
GO

Viewing 4 posts - 1 through 3 (of 3 total)

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

stax68 SSChampion Points: 11711 More actions · Answer 1

Typo in your first INSERT statement. TBL_LINK_GROUP_OBJECT_OBJECTID has an S added to make TBL_LINK_GROUP_OBJECTS_OBJECTID.

The error message and a look at the declarations section confirmed this was the case; a quick search for the offending string located it. This error would probably not have arisen if you used a consistent naming convention. Either name your tables in the singular or the plural, not a mixture.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Karma-343206 SSCrazy Points: 2104 More actions · Answer 2

Hi, Thanks for spotting that Once that was fixed I also noticed that i'd copied a couple of the temp table field name in place of the real table field name in a couple of places as well. It now parses correctly

Thanks for your time.

K

stax68 SSChampion Points: 11711 More actions · Answer 3

no problem.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant