拼接 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