0

I am trying to build a variable by passing a string parameter to evaluate a case expression and execute a dynamic SQL. But it is erroring out with: **Invalid Column Name 'abc'**

'abc' is being read as a column instead of a string, fiddled around with single quotes, double quotes around @system1 but couldn't resolve it. Any help is appreciated.

declare @sqlsystem1 nvarchar(MAX)
       ,@system1 varchar(100) = 'abc';

set @sqlsystem1 ='case when src.uid_system_of_record = ''1'' and ''' + @system1 + ''' = ''abc'' then ''201''
                       when src.uid_system_of_record = ''2'' and ''' + @system1 + ''' = ''abc'' then ''202''
                       when src.uid_system_of_record = ''3'' and ''' + @system1 + ''' = ''abc'' then ''203''
                  else src.uid_system_of_record end';

select convert(binary(32),hashbytes(''SHA2_256'', 
concat(
isnull(rtrim(convert(varchar(100), src.' + @sourcekey1 + ')),''NA''),
''|'', isnull(rtrim(convert(varchar(100),' + @sqlsystem1 + ')),''NA''), 
''|'', isnull(rtrim(convert(varchar(100), src.id)),''NA'')
)),0) as key_column 
from ' + @sourcename + ' src

Thanks.

Ked Mardemootoo
  • 1,480
  • 1
  • 5
  • 19
Kiran
  • 27
  • 5

1 Answers1

0

I have set @sqlsystem1 with the below statement

set @sqlsystem1 ='case when src.uid_system_of_record = ''1'' and ' + @system1 + ' = ''abc'' then ''201''
                       when src.uid_system_of_record = ''2'' and ' + @system1 + ' = ''abc'' then ''202''
                       when src.uid_system_of_record = ''3'' and ' + @system1 + ' = ''abc'' then ''203''
                  else src.uid_system_of_record end';

and got the output as

case 
when src.uid_system_of_record = '1' and abc = 'abc' then '201'  
when src.uid_system_of_record = '2' and abc = 'abc' then '202'    
when src.uid_system_of_record = '3' and abc = 'abc' then '203'     
else src.uid_system_of_record 
end

Remove the 2 single quotes for @system1

Harshitha Veeramalla
  • 1,515
  • 2
  • 10
  • 11
  • Thanks for the comment. When I remove the single quotes around @system1 I get the error message **Invalid Column Name 'abc'** as the system1 values is being read as abc instead of 'abc'. I have fixed the formatting by including extra quote and strangely that worked. Tried all of the combinations before but it for some reason it didn't worked. – Kiran Oct 21 '21 at 16:43