Error with table var in INNER JOIN, Stored Proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Error with table var in INNER JOIN, Stored Proc

Author

Topic

delta1186
Starting Member

10 Posts

Posted - 2007-11-07 : 12:44:05

Comment/Question
Hopefully someone can shed some light on this issue for me. I have a stored procedure that uses a declared variable @tblTemp which this table is used to loop through a comma delimited string. This works fine. However, when I try and reference this @tblTemp later in the procedure in an INNER JOIN I get the error below, and the procedure does not get created even though it says it does. What am I missing? Any help would be greatly appreciated. Thanks!

Query Analyzer
Server: Msg 137, Level 15, State 2, Procedure ImportContactsFromCRM, Line 53
Must declare the variable '@tblTemp'.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ImportContactsFromCRM'. The stored procedure will still be created.

Stored Procedure
CREATE PROCEDURE ImportContactsFromCRM
(
@intEstimateID integer,
@intBidPackageID tinyint,
@ItemIDs varchar(1000),
@strUser varchar(25)
)
AS
DECLARE @Count1 smallint
DECLARE @Count2 smallint
DECLARE @Status tinyint

SELECT @Status=intBidderStatusID FROM LU_tblStatus_Bidder WHERE strBidderStatus='No Reply'

DECLARE @tblTemp TABLE
(
ID integer,
LocID integer
)

DECLARE @Delimeter char(1) SET @Delimeter = ','
DECLARE @LocID integer
DECLARE @ID integer
DECLARE @StartPos int, @Length int
WHILE LEN(@ItemIDs) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @ItemIDs)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@ItemIDs) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @ID = SUBSTRING(@ItemIDs, 1, @StartPos - 1)
SET @ItemIDs = SUBSTRING(@ItemIDs, @StartPos + 1,LEN(@ItemIDs) - @StartPos)
END
ELSE
BEGIN
SET @ID = @ItemIDs
SET @ItemIDs = ''
END
SELECT @LocID=intLocationID FROM tblContact WHERE intContactID=@ID
INSERT @tblTemp (ID,LocID) VALUES(@ID,@LocID)
END

BEGIN
SELECT @Count1=COUNT(intCallListID) FROM tblCallList WHERE intEstimateID=@intEstimateID
AND intBidPackageID=@intBidPackageID

INSERT INTO tblCallList (intEstimateID,intBidPackageID,intBidderID,intBidderLocID,intBidderStatusID)
SELECT @intEstimateID,@intBidPackageID,ID,LocID,@Status
FROM @tblTemp
--this is what is causing the issue--------------------
INNER JOIN tblLocation ON @tblTemp.LocID = tblLocation.intLocationID
--------------------------------------------------------
WHERE tblLocation.intCompanyID NOT IN (
SELECT tblLocation.intCompanyID FROM tblCallList
INNER JOIN tblLocation ON tblCallList.intBidderLocID = tblLocation.intLocationID
WHERE intEstimateID=@intEstimateID AND intBidPackageID=@intBidPackageID
)

--old where statement which works, but does not use the @tblTemp
--WHERE ID NOT IN (
--SELECT intBidderID FROM tblCallList
--WHERE intEstimateID=@intEstimateID AND intBidPackageID=@intBidPackageID
------------------------------------------
SELECT @Count2=COUNT(intCallListID) FROM tblCallList WHERE intEstimateID=@intEstimateID
AND intBidPackageID=@intBidPackageID

IF @Count2>@Count1
BEGIN
DECLARE @strComment varchar(150)
SET @strComment = 'Bidder added to call list. (' + @strUser + ')'
INSERT INTO tblCallListLog(intCallListID,strComments)
SELECT intCallListID,@strComment FROM tblCallList
WHERE intCallListID IN (
SELECT intCallListID FROM tblCallList
WHERE intEstimateID=@intEstimateID AND intBidPackageID=@intBidPackageID)
AND intCallListID NOT IN (SELECT intCallListID FROM tblCallListLog)

RETURN 0
END
ELSE
BEGIN
RETURN 1
END
END

GO

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-07 : 12:50:35

Your BEGIN/ENDs dont match.. Reformat your code with proper indentation and you can see it..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 12:56:08

you have to create a table alias to use a qualified reference...
FROM @tblTemp AS MY_TABLE
INNER JOIN tblLocation ON MY_TABLE.LocID = tblLocation.intLocationID

delta1186
Starting Member

10 Posts

Posted - 2007-11-07 : 13:10:45

Man I love this site! Thank you anonymous1! I would have never figured that one out.

BTW, dinaker thanks for the comment but my BEGIN and END statements are correct and they are indented in my code. Unforturnately, when you post on this forum it does not retain your identations from your SQL script.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources