July 24, 2008 at 4:55 am
I was trying to compile a stored procedure
and i got this error.
[Error Code: 207, SQL State: 42S22] Invalid column name ' and b.cust_fin_entity IN ('JI','MI')'.
part of the code whre this error comes is below. Pls help out
--update the spn_name
declare @sqlStr2 varchar(5000)
select @sqlStr2 =
'update #temp_pbemea5
set spn_name = b.cust_full_name
from #temp_pbemea5 a, customer b
where a.spn_num = b.customer_id'
SELECT @sqlStr2 = @sqlStr2 + CASE @bizline
WHEN '400' THEN " and b.cust_fin_entity IN ('JI','MI')"
WHEN '450' THEN " and b.cust_fin_entity in ('HK','SI')"
WHEN '500' THEN " and b.cust_fin_entity = 'CD'"
END
--print(@sqlStr2)
exec(@sqlStr2)
Thanks in Adv
July 24, 2008 at 5:15 am
take the exec out, put the print back and see what the built up sql string looks like.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 24, 2008 at 5:53 am
It throws these errors when i try to print the sql
Server: Msg 207, Level 16, State 3, Line 10
Invalid column name ' and b.cust_fin_entity IN ('JI','MI')'.
Server: Msg 207, Level 16, State 1, Line 10
Invalid column name ' and b.cust_fin_entity in ('HK','SI')'.
Server: Msg 207, Level 16, State 1, Line 10
Invalid column name ' and b.cust_fin_entity = 'CD''.
July 24, 2008 at 6:21 am
You used double quote character for string data.
In SQL Server, that is one way to select a column name and of course you don't have a column named
[ and b.cust_fin_entity IN (''I','MI')], right?
Try this
SELECT @sqlStr2 = @sqlStr2 + CASE @bizline
WHEN '400' THEN ' and b.cust_fin_entity IN (''JI'',''MI'')'
WHEN '450' THEN ' and b.cust_fin_entity in (''HK'',''SI'')'
WHEN '500' THEN ' and b.cust_fin_entity = ''CD'''
END
N 56°04'39.16"
E 12°55'05.25"
July 24, 2008 at 7:18 am
Thanks guys..thanks to both of you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply