sql 存储过程如何动态拼接where后面的条件？

u010133107 2016-03-18 03:35:27

请教各位大侠下面的存储过程如何改？
ALTER PROCEDURE [KPI].[SP_OP001]
@IndicatorParameterID INT, --输入参数ID
@Period int, --输入周期
@Condition NVARCHAR(MAX) --输入条件(需要传入动态条件)
AS DECLARE
@SqlSelect NVARCHAR(MAX),
@SqlWhere NVARCHAR(MAX)
BEGIN
SET @SqlSelect = N'SELECT * FROM tablename '
SET @SqlWhere = 'WHERE OperationTime >= @TimeSliceStart AND OperationTime <= @TimeSliceEnd'
SET @Performance = @SqlSelect + @SqlWhere + 'AND @Condition'
EXECUTE sp_executesql @SqlSelect,@SqlWhere,@Performance;
END
假设@IndicatorParameterID 和 @Period 输入都是 6， @condition 有多个录入条件，例如：AND Code = 'FT' 或者 ANDCode = 'MT' 或者 AND Type = ‘IMP’ 或者 AND type = ‘EXP’ 在这四个条件人选一个，@TimeSliceStart 和 @TimeSliceEnd 不用管它的，因为这两个值是执行另外一个过程得到的。且@condition 参数需跟@TimeSliceStart 和 @TimeSliceEnd 一起。上面的存储过程应该怎么改？

...全文

2834 24 打赏收藏转发到动态举报

写回复

用AI写文章

24 条回复

切换为时间正序

请发表友善的回复…

发表回复

u010133107 2016-03-21

打赏
举报

使用了这句EXECUTE sp_executesql @SqlSelect, N'@TimeSliceStart datetime,@TimeSliceEnd datetime,@Performance DECIMAL(10,2) OUTPUT',@TimeSliceStart,@TimeSliceEnd,@Performance OUTPUT; 执行过程，还是录入@IndicatorParameterID = 6 ，@IndicatorParameterID =4 @Condition = TradeTypeCode = 'FT' ，报错：消息 8115，级别 16，状态 6，过程 SP_OP001，第 63 行将 nvarchar 转换为数据类型 numeric 时出现算术溢出错误。 (1 行受影响) (1 行受影响) (1 行受影响)

中国风 2016-03-21

打赏
举报

用以下测测

ALTER PROCEDURE [test].[SP_t001]   
--以下为输入参数 
@IndicatorParameterID    INT,    --输入指标参数ID   
@Period     NVARCHAR(10),        --输入周期
@Condition                  NVARCHAR(MAX)               --输入条件(需要传入动态条件)
AS
DECLARE @TimeGranularity    NVARCHAR(5),
@Year    NVARCHAR(4),
@TimeSliceStart    DATETIME,
@TimeSliceEnd    DATETIME,
@Performance    DECIMAL(10,2),
@SqlSelect          NVARCHAR(4000),
@SqlWhere           NVARCHAR(4000)
 
BEGIN  
--获取时间粒度中文值
SELECT @TimeGranularity = KPI.TimeGranularity.TimeGranularity, @Year = KPI.IndicatorParameter.Year
  FROM HyperCubeODS.KPI.TimeGranularity
 INNER JOIN KPI.IndicatorParameter ON TimeGranularity.TimeGranularityID = IndicatorParameter.TimeGranularityID
 WHERE IndicatorParameter.TimeGranularityID = @IndicatorParameterID;
 
--获取时间范围开始值和结束值
EXEC KPI.SP_GenerateTimeSlice @Year, @TimeGranularity, @Period, @TimeSliceStart OUT, @TimeSliceEnd OUT
--运行查询语句查询绩效结果
SET @SqlSelect = N'SELECT @Performance=SUM(CASE [SizeCode] WHEN ''20'' THEN 1 WHEN ''40'' THEN 2 ELSE 2.25 END) AS number  FROM tablename'
   
SET @SqlWhere = 'WHERE OperationTime >= @TimeSliceStart 
   AND OperationTime <= @TimeSliceEnd AND '
      
SET @SqlSelect = @SqlSelect + @SqlWhere + '@Condition'
 
EXECUTE sp_executesql @SqlSelect, N'@TimeSliceStart datetime,@TimeSliceEnd datetime,@Performance    DECIMAL(10,2) OUTPUT',@TimeSliceStart,@TimeSliceEnd,@Performance OUTPUT;
--判断Performance表是否存在记录
MERGE INTO Performance T1
USING (SELECT @IndicatorParameterID, @Period, @Performance) AS T2(IndicatorParameterID, Period, Performance) ON T1.IndicatorParameterID = T2.IndicatorParameterID AND T1.Period = T2.Period
 
WHEN MATCHED THEN
     
--存在则更新字段
UPDATE SET T1.Performance = T2.Performance
 
WHEN NOT MATCHED THEN
 
--不存在则插入数据
INSERT(IndicatorParameterID, Period, Performance, Creater,CreateTime)VALUES(@IndicatorParameterID, @Period, @Performance, 'admin', GETDATE());
END
GO

u010133107 2016-03-21

打赏
举报

引用 13 楼 roy_88 的回复:

ALTER PROCEDURE [test].[SP_t001]	
--以下为输入参数 
@IndicatorParameterID	INT,	--输入指标参数ID   
@Period	 NVARCHAR(10),    	--输入周期
@Condition                  NVARCHAR(MAX)               --输入条件(需要传入动态条件)
AS
DECLARE @TimeGranularity	NVARCHAR(5),
@Year	NVARCHAR(4),
@TimeSliceStart	DATETIME,
@TimeSliceEnd	DATETIME,
@Performance	DECIMAL(10,2),
@SqlSelect          NVARCHAR(500),
@SqlWhere           NVARCHAR(200)

BEGIN  
--获取时间粒度中文值
SELECT @TimeGranularity = KPI.TimeGranularity.TimeGranularity, @Year = KPI.IndicatorParameter.Year
  FROM HyperCubeODS.KPI.TimeGranularity
 INNER JOIN KPI.IndicatorParameter ON TimeGranularity.TimeGranularityID = IndicatorParameter.TimeGranularityID
 WHERE IndicatorParameter.TimeGranularityID = @IndicatorParameterID;

--获取时间范围开始值和结束值
EXEC KPI.SP_GenerateTimeSlice @Year, @TimeGranularity, @Period, @TimeSliceStart OUT, @TimeSliceEnd OUT
--运行查询语句查询绩效结果
SET @SqlSelect = N'SELECT @Performance=SUM(CASE [SizeCode] WHEN ''20'' THEN 1 WHEN ''40'' THEN 2 ELSE 2.25 END) AS number  FROM tablename'
  
SET @SqlWhere = 'WHERE OperationTime >= @TimeSliceStart 
   AND OperationTime <= @TimeSliceEnd AND '
 	
SET @Performance = @SqlSelect + @SqlWhere + '@Condition'

EXECUTE sp_executesql @Performance, N'@TimeSliceStart datetime,@TimeSliceEnd datetime,@Performance	DECIMAL(10,2) OUTPUT',@TimeSliceStart,@TimeSliceEnd,@Performance OUTPUT;
--判断Performance表是否存在记录
MERGE INTO Performance T1
USING (SELECT @IndicatorParameterID, @Period, @Performance) AS T2(IndicatorParameterID, Period, Performance) ON T1.IndicatorParameterID = T2.IndicatorParameterID AND T1.Period = T2.Period

WHEN MATCHED THEN
    
--存在则更新字段
UPDATE SET T1.Performance = T2.Performance

WHEN NOT MATCHED THEN

--不存在则插入数据
INSERT(IndicatorParameterID, Period, Performance, Creater,CreateTime)VALUES(@IndicatorParameterID, @Period, @Performance, 'admin', GETDATE());
END
GO

执行过程，录入@IndicatorParameterID = 6 ，@IndicatorParameterID =4 @Condition = TradeTypeCode = 'FT' 或者录入@IndicatorParameterID = 6 ，@IndicatorParameterID =4 @Condition = TradeTypeCode = 'MT' 还是报同一错。消息 8115，级别 16，状态 6，过程 SP_OP001，第 62 行将 nvarchar 转换为数据类型 numeric 时出现算术溢出错误。消息 214，级别 16，状态 2，过程 sp_executesql，第 3 行过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@statement'。

中国风 2016-03-21

打赏
举报

ALTER PROCEDURE [test].[SP_t001]	
--以下为输入参数 
@IndicatorParameterID	INT,	--输入指标参数ID   
@Period	 NVARCHAR(10),    	--输入周期
@Condition                  NVARCHAR(MAX)               --输入条件(需要传入动态条件)
AS
DECLARE @TimeGranularity	NVARCHAR(5),
@Year	NVARCHAR(4),
@TimeSliceStart	DATETIME,
@TimeSliceEnd	DATETIME,
@Performance	DECIMAL(10,2),
@SqlSelect          NVARCHAR(500),
@SqlWhere           NVARCHAR(200)

BEGIN  
--获取时间粒度中文值
SELECT @TimeGranularity = KPI.TimeGranularity.TimeGranularity, @Year = KPI.IndicatorParameter.Year
  FROM HyperCubeODS.KPI.TimeGranularity
 INNER JOIN KPI.IndicatorParameter ON TimeGranularity.TimeGranularityID = IndicatorParameter.TimeGranularityID
 WHERE IndicatorParameter.TimeGranularityID = @IndicatorParameterID;

--获取时间范围开始值和结束值
EXEC KPI.SP_GenerateTimeSlice @Year, @TimeGranularity, @Period, @TimeSliceStart OUT, @TimeSliceEnd OUT
--运行查询语句查询绩效结果
SET @SqlSelect = N'SELECT @Performance=SUM(CASE [SizeCode] WHEN ''20'' THEN 1 WHEN ''40'' THEN 2 ELSE 2.25 END) AS number  FROM tablename'
  
SET @SqlWhere = 'WHERE OperationTime >= @TimeSliceStart 
   AND OperationTime <= @TimeSliceEnd AND '
 	
SET @Performance = @SqlSelect + @SqlWhere + '@Condition'

EXECUTE sp_executesql @Performance, N'@TimeSliceStart datetime,@TimeSliceEnd datetime,@Performance	DECIMAL(10,2) OUTPUT',@TimeSliceStart,@TimeSliceEnd,@Performance OUTPUT;
--判断Performance表是否存在记录
MERGE INTO Performance T1
USING (SELECT @IndicatorParameterID, @Period, @Performance) AS T2(IndicatorParameterID, Period, Performance) ON T1.IndicatorParameterID = T2.IndicatorParameterID AND T1.Period = T2.Period

WHEN MATCHED THEN
    
--存在则更新字段
UPDATE SET T1.Performance = T2.Performance

WHEN NOT MATCHED THEN

--不存在则插入数据
INSERT(IndicatorParameterID, Period, Performance, Creater,CreateTime)VALUES(@IndicatorParameterID, @Period, @Performance, 'admin', GETDATE());
END
GO

u010133107 2016-03-21

打赏
举报

各位大侠帮忙看下，还是调试不出来。

道素 2016-03-21

打赏
举报

按照你的写法，存储过程中应该是这样吧把变量放到单引号里不是把变量名当成字符串处理了吗


SET @SqlSelect = N'SELECT * FROM tablename '
 SET @SqlWhere =  'WHERE OperationTime >= '+@TimeSliceStart +'	 AND OperationTime <= '+@TimeSliceEnd
 SET @Performance = @SqlSelect + @SqlWhere + 'AND '+@Condition
EXECUTE sp_executesql @SqlSelect,@SqlWhere,@Performance;

u010133107 2016-03-21

打赏
举报

引用 9 楼 ch21st 的回复:

你如果直接将条件传进来执行，有可能不好控制，比如外部可以传入 1=1 其实如果条件字段是范围是确定的，可以不用动态语句实现，比如可以这样写. 你的写法比较自由，但容易失控
DECLARE @Code VARCHAR(100),@Type VARCHAR(100)
SELECT * FROM t1 WHERE t1.Code=ISNULL(@Code,t1.Code) AND t1.Type=ISNULL(@Type,t1.Type)

过程只有两个条件是固定@IndicatorParameterID , @Period ，其他例如：开始/结束时间，条件这三个参数都是可变的。

道素 2016-03-21

打赏
举报

你如果直接将条件传进来执行，有可能不好控制，比如外部可以传入 1=1 其实如果条件字段是范围是确定的，可以不用动态语句实现，比如可以这样写. 你的写法比较自由，但容易失控


DECLARE @Code VARCHAR(100),@Type VARCHAR(100)
SELECT * FROM t1 WHERE t1.Code=ISNULL(@Code,t1.Code) AND t1.Type=ISNULL(@Type,t1.Type)

u010133107 2016-03-21

打赏
举报

引用 7 楼 wmxcn2000 的回复:


-- 大概这个样子 
-- 

EXECUTE sp_executesql @Performance , N'@starttime varchar(10),@endtime varchar(20)', @starttime=N'1111' ,@endtime = N'ccc'

开始跟结束时间是由另外一个存储过程得到的。 EXECUTE sp_executesql @Performance, N'@TimeSliceStart DATETIME, @TimeSliceEnd DATETIME',@TimeSliceStart ,@TimeSliceEnd 写成这样，执行过程，录入@IndicatorParameterID = 6 ，@IndicatorParameterID =4 @Condition = TradeTypeCode = 'FT' 还是报错。消息 8115，级别 16，状态 6，过程 SP_OP001，第 62 行将 nvarchar 转换为数据类型 numeric 时出现算术溢出错误。消息 214，级别 16，状态 2，过程 sp_executesql，第 3 行过程需要类型为 'ntext/nchar/nvarchar' 的参数 '@statement'。 (1 行受影响) (1 行受影响

卖水果的net 版主 2016-03-21

打赏
举报


-- 大概这个样子 
-- 

EXECUTE sp_executesql @Performance , N'@starttime varchar(10),@endtime varchar(20)', @starttime=N'1111' ,@endtime = N'ccc'

u010133107 2016-03-21

打赏
举报

下面是所有整个过程 ALTER PROCEDURE [test].[SP_t001] --以下为输入参数 @IndicatorParameterID INT, --输入指标参数ID @Period NVARCHAR(10), --输入周期 @Condition NVARCHAR(MAX) --输入条件(需要传入动态条件) AS DECLARE @TimeGranularity NVARCHAR(5), @Year NVARCHAR(4), @TimeSliceStart DATETIME, @TimeSliceEnd DATETIME, @Performance DECIMAL(10,2), @SqlSelect NVARCHAR(500), @SqlWhere NVARCHAR(200) BEGIN --获取时间粒度中文值 SELECT @TimeGranularity = KPI.TimeGranularity.TimeGranularity, @Year = KPI.IndicatorParameter.Year FROM HyperCubeODS.KPI.TimeGranularity INNER JOIN KPI.IndicatorParameter ON TimeGranularity.TimeGranularityID = IndicatorParameter.TimeGranularityID WHERE IndicatorParameter.TimeGranularityID = @IndicatorParameterID; --获取时间范围开始值和结束值 EXEC KPI.SP_GenerateTimeSlice @Year, @TimeGranularity, @Period, @TimeSliceStart OUT, @TimeSliceEnd OUT --运行查询语句查询绩效结果 SET @SqlSelect = N'SELECT SUM(CASE [SizeCode] WHEN ''20'' THEN 1 WHEN ''40'' THEN 2 ELSE 2.25 END) AS number FROM tablename SET @SqlWhere = 'WHERE OperationTime >= @TimeSliceStart AND OperationTime <= @TimeSliceEnd AND ' SET @Performance = @SqlSelect + @SqlWhere + '@Condition' EXECUTE sp_executesql @SqlSelect, N'@TimeSliceStart datetime,@TimeSliceEnd datetime',@Performance; --判断Performance表是否存在记录 MERGE INTO Performance T1 USING (SELECT @IndicatorParameterID, @Period, @Performance) AS T2(IndicatorParameterID, Period, Performance) ON T1.IndicatorParameterID = T2.IndicatorParameterID AND T1.Period = T2.Period WHEN MATCHED THEN --存在则更新字段 UPDATE SET T1.Performance = T2.Performance WHEN NOT MATCHED THEN --不存在则插入数据 INSERT(IndicatorParameterID, Period, Performance, Creater,CreateTime)VALUES(@IndicatorParameterID, @Period, @Performance, 'admin', GETDATE()); END GO

u010133107 2016-03-21

打赏
举报

EXECUTE sp_executesql @SqlSelect, N'@TimeSliceStart datetime,@TimeSliceEnd datetime',@Performance; 最后一句这样写吗？还是报错：将 nvarchar 转换为数据类型 numeric 时出现算术溢出错误。消息 8178，级别 16，状态 1，第 2 行参数化查询 '(@TimeSliceStart datetime,@TimeSliceEnd datetime)SELECT SUM(CASE' 需要参数 '@TimeSliceEnd'，但未提供该参数。

中国风 2016-03-21

打赏
举报

最好贴完整 @IndicatorParameterID , @Period 参数没用到？没用到时不需要声明 DECLARE @TimeSliceStart DATETIME ,@TimeSliceEnd DATETIME SELECT @TimeSliceEnd = GETDATE() ,@TimeSliceStart = DATEADD(mm , -1 , @TimeSliceEnd) SET @SqlSelect = N'SELECT * FROM tablename ' SET @SqlWhere = 'WHERE OperationTime >= @TimeSliceStart AND OperationTime <= @TimeSliceEnd' SET @Performance = @SqlSelect + @SqlWhere + ' AND ' + @Condition EXECUTE sp_executesql @Performance , N'@TimeSliceStart DATETIME, @TimeSliceEnd DATETIME' , @TimeSliceStart ,@TimeSliceEnd

中国风 2016-03-21

打赏
举报

@TimeSliceStart, @TimeSliceEnd 参照在外层没用声明

Ginnnnnnnn 2016-03-21

打赏
举报

EXECUTE sp_executesql @SqlSelect,@SqlWhere,@Performance; 这里有问题 @SqlWhere 这里面应该填 N'@TimeSliceStart datetime,@TimeSliceEnd' datetime', 最后一个参数填@TimeSliceStart,@TimeSliceEnd 这样子才对

u010133107 2016-03-21

打赏
举报

汗，没人回答

u010133107 2016-03-21

打赏
举报

引用 23 楼 roy_88 的回复:

SET @SqlSelect = @SqlSelect + @SqlWhere + @Condition --引号去掉

感谢版主。太谢谢了。

中国风 2016-03-21

打赏
举报

SET @SqlSelect = @SqlSelect + @SqlWhere + @Condition --引号去掉

u010133107 2016-03-21

打赏
举报

SET @Performance= @SqlSelect + @SqlWhere + '@Condition' ----》改为 SET @SqlSelect = @SqlSelect + @SqlWhere + '@Condition' exec [KPI].[SP_OP001] 6,6,'TradeTypeCode = ''DT''' 提示必须声明标量变量 "@Condition"。

中国风 2016-03-21

打赏
举报

再试试，刚发现有一处赋值不正确有别名

ALTER PROCEDURE [test].[SP_t001]   
--以下为输入参数 
@IndicatorParameterID    INT,    --输入指标参数ID   
@Period     NVARCHAR(10),        --输入周期
@Condition                  NVARCHAR(MAX)               --输入条件(需要传入动态条件)
AS
DECLARE @TimeGranularity    NVARCHAR(5),
@Year    NVARCHAR(4),
@TimeSliceStart    DATETIME,
@TimeSliceEnd    DATETIME,
@Performance    DECIMAL(30,2),
@SqlSelect          NVARCHAR(4000),
@SqlWhere           NVARCHAR(4000)
  
BEGIN  
--获取时间粒度中文值
SELECT @TimeGranularity = KPI.TimeGranularity.TimeGranularity, @Year = KPI.IndicatorParameter.Year
  FROM HyperCubeODS.KPI.TimeGranularity
 INNER JOIN KPI.IndicatorParameter ON TimeGranularity.TimeGranularityID = IndicatorParameter.TimeGranularityID
 WHERE IndicatorParameter.TimeGranularityID = @IndicatorParameterID;
  
--获取时间范围开始值和结束值
EXEC KPI.SP_GenerateTimeSlice @Year, @TimeGranularity, @Period, @TimeSliceStart OUT, @TimeSliceEnd OUT
--运行查询语句查询绩效结果
SET @SqlSelect = N'SELECT @Performance=SUM(CASE [SizeCode] WHEN ''20'' THEN 1 WHEN ''40'' THEN 2 ELSE 2.25 END)   FROM tablename'
    
SET @SqlWhere = 'WHERE OperationTime >= @TimeSliceStart 
   AND OperationTime <= @TimeSliceEnd AND '
       
SET @SqlSelect = @SqlSelect + @SqlWhere + '@Condition'
  
EXECUTE sp_executesql @SqlSelect, N'@TimeSliceStart datetime,@TimeSliceEnd datetime,@Performance    DECIMAL(10,2) OUTPUT',@TimeSliceStart,@TimeSliceEnd,@Performance OUTPUT;
--判断Performance表是否存在记录
MERGE INTO Performance T1
USING (SELECT @IndicatorParameterID, @Period, @Performance) AS T2(IndicatorParameterID, Period, Performance) ON T1.IndicatorParameterID = T2.IndicatorParameterID AND T1.Period = T2.Period
  
WHEN MATCHED THEN
      
--存在则更新字段
UPDATE SET T1.Performance = T2.Performance
  
WHEN NOT MATCHED THEN
  
--不存在则插入数据
INSERT(IndicatorParameterID, Period, Performance, Creater,CreateTime)VALUES(@IndicatorParameterID, @Period, @Performance, 'admin', GETDATE());
END
GO