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