Not Replace @TFYID Variable with value in SQL Query

Question

set @TFYID=7
print @TFYID

set @SQL =N'select @ETotal=sum(ExemptionProduce) 
            from   tbl_Income_Exemption 
            where  EmployeeID='''+@EmpID+'''  and (TDSSettingsDetailID) in 
                  ('+@ConcatString+''') and FinancialYearID='+@TFYID+''

exec sp_executesql @SQL

output :

7
Msg 245, Level 16, State 1, Line 73
Conversion failed when converting the nvarchar value '
select @ETotal=sum(ExemptionProduce) 
from   tbl_Income_Exemption 
where  EmployeeID='00402060'  and (TDSSettingsDetailID) in 
       ('24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36') 
       and FinancialYearID=' to data type int.

This Query not showing me properly output. it's give me some error. Please Help me.

score 0 · Accepted Answer · answered Jul 08 '17 at 09:59

The way you have written the query you need to convert the value to a string:

set @SQL =N'select @ETotal=sum(ExemptionProduce) 
            from   tbl_Income_Exemption 
            where  EmployeeID='''+cast(@EmpID as varchar(255))+'''  and (TDSSettingsDetailID) in 
                  ('+@ConcatString+''') and FinancialYearID='+cast(@TFYID as varchar(255))+''

exec sp_executesql @SQL

However, the correct way to write the query is to use parameters -- at least where you can:

declare @ETotal decimal(18, 4);  -- or whatever

set @SQL = N'
select @ETotal = sum(ExemptionProduce) 
from tbl_Income_Exemption 
where EmployeeID = @EmpID and
      TDSSettingsDetailID in (' + @ConcatString + ') and 
      FinancialYearID = @TFYID';

exec sp_executesql @SQL,
     N'@ETotal decimal(18, 4) output, @EmpId int, @TFYID int',
     @ETotal = @ETotal output, @EmpId = @EmpId, @TFYID = @TFYID;

You cannot pass the in list as a single parameter, so munging the query string is a viable option.

Whether this works or not depends on the values and types of things that you haven't explained in the question. But you should be able to get the right idea.

Not Replace @TFYID Variable with value in SQL Query

1 Answers1