Weird SQL 2005 problem - Store Procedure

[christer102]

The Storeprocedure works fine in SQL 2000, but not in SQL 2005. I am getting the error: Invalid column name '19' when running the Storeprocedure. However, if I just run this as query it works fine (on both 2005 and 2000). Once it is a store procedure, and I call for example dbo.testprocedure then it is giving the invalid column name error.


SET QUOTED_IDENTIFIER OFF

declare @SQL VARCHAR(7900), @COUNTVAR VARCHAR(1) ,@COUNTER INT
set @COUNTVAR='0'

set @counter = 0
while @counter < 6
begin
SET @COUNTVAR =CONVERT(VARCHAR(1),@counter)

SET @SQL='UPDATE OPDETAILS
SET TYPEID = B.MINPTID, ESTPREM=B.PREMIUM, ESTCLOSE= (case when isdate(B.EXPVAL)=1 then B.EXPVAL else ESTCLOSE end), LEADSOURCE= (CASE WHEN B.LID IS NOT NULL and B.TME is not null THEN B.LID ELSE LEADSOURCE end) FROM
(SELECT PROSP.REC, MIN(PolicyType_1.PTID) AS MINPTID, ISNULL(PROSP.PREM'+@COUNTVAR+',0) AS PREMIUM,

(CASE WHEN ASCII(substring(PROSP.EXP'+@COUNTVAR+', 7, 1)) BETWEEN 49 AND 57 THEN
(CONVERT(varchar(10), LEFT(PROSP.EXP'+@COUNTVAR+', 6) + convert(varchar(2),"19") + RIGHT(PROSP.EXP'+@COUNTVAR+', 2))) when ISDATE(CONVERT(varchar(10), CONVERT(nvarchar(6), LEFT(PROSP.EXP'+@COUNTVAR+', 6)) + CONVERT(nvarchar(4),
ASCII(SUBSTRING(PROSP.EXP'+@COUNTVAR+', 7, 1)) + 135)) + RIGHT(PROSP.EXP'+@COUNTVAR+', 1)) = 1 THEN
(CONVERT(varchar(10), CONVERT(nvarchar(6), LEFT(PROSP.EXP'+@COUNTVAR+', 6))
+ CONVERT(nvarchar(4), ASCII(SUBSTRING(PROSP.EXP'+@COUNTVAR+', 7, 1)) + 135)) + RIGHT(PROSP.EXP'+@COUNTVAR+', 1))
ELSE null END) AS EXPVAL, LeadSource.LID, '+@COUNTVAR+' as CVAR, LeadSource.TME
FROM PolicyType PolicyType_1 RIGHT OUTER JOIN
(SELECT PTID, 1 AS BB
FROM PROSP) ABC INNER JOIN
PROSP ON ABC.PTID = PROSP.PTID LEFT OUTER JOIN
OpDetails ON ABC.BB = OpDetails.TPOS AND PROSP.REC = OpDetails.ACCREC ON PolicyType_1.TYPE = PROSP.TYPE'+@COUNTVAR+' LEFT OUTER JOIN
LeadSource ON PROSP.MPLAN = LeadSource.TME
GROUP BY PROSP.REC, PROSP.PREM'+@COUNTVAR+', PROSP.EXP'+@COUNTVAR+', LeadSource.LID, LeadSource.TME) B
WHERE B.REC=OPDETAILS.ACCREC AND B.CVAR=OPDETAILS.TPOS '

EXEC(@SQL)
set @counter = @counter + 1

end

 

[LNBruno]

Perhaps here:

convert(varchar(2),[b][red]"19"[/red][/b])

< M!ke >
[small]"Oops" is never a good thing.[/small]