vendredi 11 mars 2016

How to properly concatenate and escape strings in T-SQL

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