Replacing sql seems to be causing failure

Question

Hi,

I have the following tsql code in SSIS.

t.NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,',','') as int),0),

The line worked before I added the replace...

Am I using it correctly?

Full SQL is

declare @t nvarchar(max)
declare @tablename nvarchar(128)
select @tablename = ?

select @t = 'Update t
set t.CampaignCode = s.CampaignCode,
t.CampaignDescription = s.CampaignDescription,
t.MediaDescription = s.MediaDescription,
t.NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,',','') as int),0),
t.PromotionCode = s.PromotionCode,
t.PromotionDescription = s.PromotionDescription
FROM ' + @tablename +  '  t
left outer join
MaginusStaging.dbo.MediaCode_CampaignCode s
on
t.MaginusMediaCode = s.MediaCode
where t.MaginusMediaCode IS NOT NULL'

exec sp_executesql @t

Thanks for help.

Answer 1

Please try the following.
The issue was pertaining to the single quotes inside single quotes overall.
They need to be doubled.

SQL

DECLARE @t NVARCHAR(MAX);
DECLARE @tablename NVARCHAR(128);
SET @tablename = ?

SET @t = 'UPDATE t
 SET t.CampaignCode = s.CampaignCode,
 t.CampaignDescription = s.CampaignDescription,
 t.MediaDescription = s.MediaDescription,
 t.NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,'','','''') AS INT), 0),
 t.PromotionCode = s.PromotionCode,
 t.PromotionDescription = s.PromotionDescription
 FROM ' + @tablename +  '  t
 LEFT OUTER JOIN
 MaginusStaging.dbo.MediaCode_CampaignCode s
 ON t.MaginusMediaCode = s.MediaCode
 WHERE t.MaginusMediaCode IS NOT NULL';

Answer 2

Remove the 't.'

 t.NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,',','') as int),0),

Should be:

NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,',','') as int),0),