I'm trying to create a stored procedure from an sql string that i'm creating by concatenating multiple strings.
Getting the string escaping is giving me some trouble and i thought some one might sport the places where i'm getting it wrong.
When i try to execute the script i get the following errors
Msg 102, Level 15, State 1, Procedure TEST_01, Line 48 Incorrect syntax near '@StartDate'.
Msg 102, Level 15, State 1, Procedure TEST_01, Line 50 Incorrect syntax near '@MenuIds'.
Msg 102, Level 15, State 1, Procedure TEST_01, Line 53 Incorrect syntax near '@MeansIds'.
Msg 102, Level 15, State 1, Procedure TEST_01, Line 56 Incorrect syntax near '@TypeIds'.
Msg 102, Level 15, State 1, Procedure TEST_01, Line 59 Incorrect syntax near '@CondIds'.
Msg 102, Level 15, State 1, Procedure TEST_01, Line 64 Incorrect syntax near '+MeansIdentifier+'.
i have tried to play a round the string escaping but the more i try the more i mess it up.
Below is my script.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @CreateSP varchar(MAX)
SET @CreateSP = '
CREATE PROCEDURE [dbo].[TEST_01]
(
@StartDate DATETIME,
@EndDate DATETIME,
@MenuIds NVARCHAR(MAX),
@MeansIds NVARCHAR(MAX),
@TypeIds NVARCHAR(MAX),
@CondIds NVARCHAR(MAX),
@CatIds NVARCHAR(MAX),
@Blocks NVARCHAR(MAX)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql AS NVARCHAR(MAX);
SET @sql = '';WITH CTE_ActionDetails
AS (
SELECT
CASE(GROUPING(M.CodeName))
WHEN 0 THEN [CodeName]
WHEN 1 THEN ''''Total''''
END AS [Menu],
CASE(GROUPING(D.[Name]))
WHEN 0 THEN D.[Name]
WHEN 1 THEN ''''ET-Total ''''+ ''''(''''+CC.Name+'''')''''
END AS [Name],
max(C.MeansIdentifier) AS MeansIdentifier,
MAX(D.Name) as [Condition],
SUM(ISNULL(Master.Youth, 0)) AS Youth,
SUM(ISNULL(Master.MiddleAged, 0)) AS MiddleAged,
SUM(ISNULL(Master.Elderly, 0)) AS Elderly,
ISNULL(max(Master.Comment), '''') AS Comment,
GROUPING(M.CodeName) AS MenuGrouping,
GROUPING(C.MeansIdentifier) AS MeansGrouping,
GROUPING(CC.Name) AS TypeGrouping,
GROUPING(D.[Name]) AS ItemGrouping
FROM Condition D
INNER JOIN MasterData Master ON Master.ConditionId = D.ConditionId
INNER JOIN Report R ON R.ReportId = Master.ReportId
INNER JOIN Menu M ON R.MenuId = M.MenuId
INNER JOIN Means C ON C.MeansId = Master.MeansId
INNER JOIN Type CC ON CC.TypeId = Master.TypeId
WHERE (1=1)''
SET @sql = @sql + '' AND (R.ReportDate >= ''@StartDate'' AND R.ReportDate <=''@EndDate'')''
SET @sql = @sql + '' AND (R.MenuId IN IN (SELECT DATA FROM SPLIT(''@MenuIds,'','''')))''
IF @MeansIds is not null
SET @sql = @sql + '' AND (Master.MeansId IN (SELECT DATA FROM SPLIT(''@MeansIds,'','''')))''
IF @TypeIds is not null
SET @sql = @sql + '' AND (Master.TypeId IN (SELECT DATA FROM SPLIT(''@TypeIds,'','''')))''
IF @CondIds is not null
SET @sql = @sql + '' AND (Master.ConditionId IN (SELECT DATA FROM SPLIT(''@CondIds,'','''')))''
SET @sql = @sql + '' GROUP BY M.CodeName,C.MeansIdentifier,CC.Name,D.Name WITH ROLLUP ''
SET @sql = @sql + '' SELECT Menu,
(CASE WHEN [Name] IS NULL THEN ''''Total '''' + ''(''+MeansIdentifier+'')''
WHEN [Name] IS NOT NULL THEN ''''
END) AS MeansIdentifier,
[Name] AS [Condition],
Youth,
Elderly,
MiddleAged,
(Youth + Elderly + MiddleAged) AS Total,
Comment
FROM CTE_ActionDetails
END'''
EXEC(@CreateSP)
Aucun commentaire:
Enregistrer un commentaire