Replacing sql seems to be causing failure

Christopher Jack 1,611 Reputation points
2021-02-21T21:08:17.913+00:00

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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,486 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,381 Reputation points
    2021-02-23T16:29:29.723+00:00

    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';
    
    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Jeffrey Williams 1,891 Reputation points
    2021-02-22T20:51:08.683+00:00

    Remove the 't.'

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

    Should be:

    NoOfCatalogs = ISNULL(CAST(replace(s.NoOfCats,',','') as int),0),
    
    1 person found this answer helpful.
    0 comments No comments