|
delta1186
Starting Member
USA
10 Posts |
 Posted - 11/07/2007 : 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 |
Edited by - delta1186 on 11/07/2007 12:46:01
|
|
|
delta1186
Starting Member
USA
10 Posts |
Posted - 11/07/2007 : 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. |
 |
|
Error with table var in INNER JOIN, Stored Proc
