SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Error with table var in INNER JOIN, Stored Proc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

delta1186
Starting Member

USA
10 Posts

Posted - 11/07/2007 :  12:44:05  Show Profile  Reply with Quote
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

Edited by - delta1186 on 11/07/2007 12:46:01

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 11/07/2007 :  12:50:35  Show Profile  Visit dinakar's Homepage  Reply with Quote
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/
Go to Top of Page

anonymous1
Posting Yak Master

185 Posts

Posted - 11/07/2007 :  12:56:08  Show Profile  Reply with Quote
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
Go to Top of Page

delta1186
Starting Member

USA
10 Posts

Posted - 11/07/2007 :  13:10:45  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000