我想实现interMintue为不同值时执行不同方式的查询,存储过程如下所示,但是出现如下的错误,请问怎么修改
谢谢
ALTER PROCEDURE [dbo].[up_inqRealTimeDataByInterMintue]
@ldBeginNo char(20),
@ldEndNo char(20),
@interMintue int,
@beginHour char(2) = '00'AS CASE interMintue WHEN 0 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo
WHEN 1 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,1) IN('0','5')
WHEN 2 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,1) IN('0')
WHEN 3 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,2) IN('00','30')
WHEN 4 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,16,2) IN('0')
WHEN 5 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,16,2) = @beginHour
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第22 行
关键字'CASE' 附近有语法错误。
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第23 行
关键字'WHEN' 附近有语法错误。
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第24 行
关键字'WHEN' 附近有语法错误。
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第25 行
关键字'WHEN' 附近有语法错误。
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第26 行
关键字'WHEN' 附近有语法错误。
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第27 行
关键字'WHEN' 附近有语法错误。
谢谢
ALTER PROCEDURE [dbo].[up_inqRealTimeDataByInterMintue]
@ldBeginNo char(20),
@ldEndNo char(20),
@interMintue int,
@beginHour char(2) = '00'AS CASE interMintue WHEN 0 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo
WHEN 1 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,1) IN('0','5')
WHEN 2 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,1) IN('0')
WHEN 3 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,2) IN('00','30')
WHEN 4 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,16,2) IN('0')
WHEN 5 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,16,2) = @beginHour
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第22 行
关键字'CASE' 附近有语法错误。
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第23 行
关键字'WHEN' 附近有语法错误。
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第24 行
关键字'WHEN' 附近有语法错误。
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第25 行
关键字'WHEN' 附近有语法错误。
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第26 行
关键字'WHEN' 附近有语法错误。
消息156,级别15,状态1,过程up_inqRealTimeDataByInterMintue,第27 行
关键字'WHEN' 附近有语法错误。
把case 语句都 换成 if () else
--try:
ALTER PROCEDURE [dbo].[up_inqRealTimeDataByInterMintue]
@ldBeginNo char(20),
@ldEndNo char(20),
@interMintue int,
@beginHour char(2) = '00' AS
SELECT * from 表名 where ld_no BETWEEN @ldBeginNo AND @ldEndNo and
CASE interMintue
WHEN 1 THEN SUBSTRING(ld_no,17,1) IN('0','5')
WHEN 2 THEN SUBSTRING(ld_no,17,1) IN('0')
WHEN 3 THEN SUBSTRING(ld_no,17,2) IN('00','30')
WHEN 4 THEN SUBSTRING(ld_no,16,2) IN('0')
WHEN 5 THEN SUBSTRING(ld_no,16,2) = @beginHour end
ALTER PROCEDURE [dbo].[up_inqRealTimeDataByInterMintue]
@ldBeginNo char(20),
@ldEndNo char(20),
@interMintue int,
@beginHour char(2) = '00' AS if( interMintue = 0)
begin
SELECT * from 表名 WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo
end
if( interMintue = 1)
begin
SELECT * from 表名 WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,1) IN('0','5')
end
if( interMintue = 2)
begin
SELECT * from 表名 WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,1) IN('0')
end
if( interMintue = 3)
begin
SELECT * from 表名 WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,2) IN('00','30')
end
if( interMintue = 4)
begin
SELECT * from 表名 WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,16,2) IN('0')
end
if( interMintue = 5)
begin
SELECT * from 表名 WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,16,2) = @beginHour
end
return
@ldBeginNo char(20),
@ldEndNo char(20),
@interMintue int,
@beginHour char(2) = '00' AS
declare @sql varchar(100)
set @sql=CASE interMintue
WHEN 1 THEN ' and SUBSTRING(ld_no,17,1) IN(''0'',''5'')'
WHEN 2 THEN ' and SUBSTRING(ld_no,17,1) IN(''0'')'
WHEN 3 THEN ' and SUBSTRING(ld_no,17,2) IN(''00'',''30'')'
WHEN 4 THEN ' and SUBSTRING(ld_no,16,2) IN(''0'')'
WHEN 5 THEN ' and SUBSTRING(ld_no,16,2) = '''+ @beginHour+'''' end
exec('SELECT * from 表名 where ld_no BETWEEN '''+@ldBeginNo+''' AND '''+@ldEndNo +''' '+@sql
我修改过后的代码如下:
ALTER PROCEDURE [dbo].[up_inqRealTimeDataByInterMintue]
@ldBeginNo char(20),
@ldEndNo char(20),
@interMintue int,
@beginHour char(2) = '00'AS if @interMintue = 0 SELECT * from STData WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo
else if @interMintue = 1 SELECT * from STData WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,1) IN('0','5')
else if @interMintue = 2 SELECT * from STData WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,1) IN('0')
else if @interMintue = 3 SELECT * from STData WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,2) IN('00','30')
else if @interMintue = 4 SELECT * from STData WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,16,2) IN('0')
else if @interMintue = 5 SELECT * from STData WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,16,2) = @beginHour
但是IN后面这样用可以吗例如IN('00','30')
CASE interMintue WHEN 0 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo
WHEN 1 THEN SELECT * WHERE ld_no BETWEEN @ldBeginNo AND @ldEndNo AND SUBSTRING(ld_no,17,1) IN('0','5')
/**很明显你的then 返回的结果超过1条当然报错。**/
没用过这种用法,呵呵