mardi 19 janvier 2016

How to properly escape strings inside an T-QSL query which itself a string

I'm trying to create an SQL variable which will hold an SQL string that i will execute by calling EXEC(myVariable)

I have the below SQL script to create a stored procedure but when i execute it in management studio, it throws the following error

The replace function requires 3 argument(s).

Below is the full script

IF NOT EXISTS ( SELECT  * FROM    sys.objects WHERE   object_id = OBJECT_ID(N'SP_Test') AND type IN (N'P', N'SP_Test')) 
BEGIN
DECLARE @CreateSP varchar(MAX) = '
CREATE  PROCEDURE [dbo].[SP_Test]       
 (@Destination varchar(100),      
 @Period varchar(8000),      
 @ItemType varchar(100),      
 @Nationality  varchar(100))   

AS      
BEGIN      
 SET NOCOUNT ON;      

 Declare @sql as nvarchar(4000)   

 set @sql = ''SELECT distinct  Country.Country_ID,Country.Name, Destination.Destination_ID, Destination.Destination_Name,
   MyTableName.Year, Region.Region_ID,  rtrim(Region.Region_Name) as Category,    
 Count(MyTableName.Allegation) as AllegCnt INTO ##MyTableName_Temp
 FROM MyTableName INNER JOIN Destination on MyTableName.Destination_ID = Destination.Destination_ID
 INNER JOIN Country on  MyTableName.Country_ID=Country.Country_ID
  INNER JOIN Region on MyTableName.Region_ID=Region.Region_ID 
  where 1=1 and Country.Lang_ID=1200 
  AND MyTableName.Country_ID NOT LIKE ''%N/A%'' AND MyTableName.Region_ID != 1''   

 if @Destination is not  null      
 set @sql = @sql + '' AND MyTableName.Destination_ID IN ('' +@Destination+'')''      


 if @Period is not null      
 set @sql = @sql + '' AND Year IN ('' +@Period+'')''      


 if @ItemType is not null      
 set @sql = @sql + '' AND MyTableName.Region_ID IN ('' +@ItemType+'')''      


 if @Nationality  is not null      
 set @sql = @sql + '' AND MyTableName.Country_ID IN ('''''' +replace(@Nationality,'','','''','''')+''''+'')''   


 set @sql = @sql + '' GROUP BY MyTableName.Year,Destination.Destination_Name, Destination.Destination_ID,Country.Country_ID,Country.Name,Region.Region_ID,  Region.Region_Name       
 ORDER BY Name desc''      

 execute(@sql)      

 EXECUTE sp_Arrange      
 ''SELECT Name as Origin, Category, Total FROM ##MyTableName_Temp'',      
 NULL,      
NULL,      
''Origin'',
 ''Total'',    
 ''SUM''         
  drop table ##MyTableName_Temp      
END'      
 EXEC(@CreateSP) 
END

It looks like i'm not escaping the strings in the script properly.

Aucun commentaire:

Enregistrer un commentaire