存储过程拼接sql

拼接 SQL的时候有单引号的话， 需要再用单引号进行转义  

ROCEDURE [dbo].[P_XXXX]
(
    --参数
    @RegionCode varchar(10), --区域
    @StartDate varchar(50), --开始时间
    @EndDate varchar(50) --结束时间
)
AS
BEGIN

SET NOCOUNT ON;

--声明变量
declare @code nvarchar(20)
declare @sql nvarchar(4000)
declare @QueryTimeSql nvarchar(1000)
declare @QueryEndTimeSql nvarchar(1000)

SET @code= CASE WHEN @RegionCode ='BJ' THEN  'SCqMgr' WHEN @RegionCode ='TG' THEN 'CTC' WHEN @RegionCode ='TP' THEN 'CBP' ELSE '' END  --根据入参给变量赋值

IF((@StartDate IS NULL OR @StartDate='') OR (@EndDate IS NULL OR @EndDate='')) -- 入参为空，则条件sql为空
   BEGIN
        SET @QueryTimeSql=''
        SET @QueryEndTimeSql=''
   END
ELSE
   BEGIN  
        --入参不为空， 则编辑条件sql
        SET @QueryTimeSql=N' WHERE (SELECT MAX(OperDate) OperDate FROM  dbo.FlowLog'+@RegionCode+' AS fl WHERE fl.WorkSheetId=log.WorkSheetId AND ProcedureCode=''Close'')>='''+@StartDate+'''
        AND (SELECT MAX(OperDate) OperDate FROM  dbo.FlowLog'+@RegionCode+' AS fl WHERE fl.WorkSheetId=log.WorkSheetId AND ProcedureCode=''Close'')<='''+@EndDate+'''
        '
        SET @QueryEndTimeSql=N' WHERE FL.EndDate>='''+@StartDate+''' AND FL.EndDate<='''+@EndDate+'''
        '
   END

--sql 主体部分
SET @sql=N'  
WITH FlowLog'+@RegionCode+' AS 
    (
        SELECT 
             WorkSheetId
              ,StartDate = (SELECT MIN(OperDate) OperDate FROM  dbo.FlowLog'+@RegionCode+' AS fl WHERE fl.WorkSheetId=log.WorkSheetId AND ProcedureCode='''+@code+''')
            ,EndDate=(SELECT MAX(OperDate) OperDate FROM  dbo.FlowLog'+@RegionCode+' AS fl WHERE fl.WorkSheetId=log.WorkSheetId AND ProcedureCode=''Close'')
        FROM dbo.FlowLog'+@RegionCode+'  AS log 
        '+@QueryTimeSql+'
        GROUP BY log.WorkSheetId
    )
    ,R1 AS 
    (
        SELECT 
              COUNT(WS.WorkSheetId) AS TotalCount
             ,[dbo].[F_ConvertToDay](SUM(  CAST( CASE  WHEN FL.StartDate IS NULL OR FL.EndDate IS NULL THEN 0 
                                ELSE  DATEDIFF(SS,FL.StartDate,FL.EndDate
                        ) END  AS  decimal(10)))) AS SumTime
             ,[dbo].[F_ConvertToDay](SUM(  CAST( CASE  WHEN FL.StartDate IS NULL OR FL.EndDate IS NULL THEN 0 
                                ELSE  DATEDIFF(SS,FL.StartDate,FL.EndDate
                        ) END  AS  decimal(10)))/COUNT(WS.WorkSheetId)) AS SingleTime
        FROM  dbo.WorkSheet'+@RegionCode+'  AS WS
        LEFT OUTER JOIN  FlowLog'+@RegionCode+' AS FL ON  WS.Id=FL.WorkSheetId  '+@QueryEndTimeSql+'
    ) 
    SELECT * FROM R1 '
    execute sp_executesql @sql Output  --输出查询结果
END
GO