create table t(id int,expression varchar(500),paramvalue int) insert into t select 1,'@param > 3',1 union all select 2,'@param < 3',2 create proc p_validexpression(@value int,@paramvalue int output) as declare @i tinyint declare @s nvarchar(500) declare @expression nvarchar(500) declare cur_tmp cursor for select expression,paramvalue from t open cur_tmp fetch next from cur_tmp into @expression,@paramvalue while @@fetch_status=0 begin select @s='if ('+replace(@expression,'@param',rtrim(@value))+') set @i=1 else set @i=0' exec sp_executesql @s,N'@i tinyint output',@i output if @i=1 goto a fetch next from cur_tmp into @expression,@paramvalue end a: close cur_tmp deallocate cur_tmp GOdeclare @i int exec p_validexpression 4,@i output print @i --1declare @i int exec p_validexpression 2,@i output print @i --2
create proc p_validexpression(@value int,@paramvalue int output) as declare @i tinyint declare @s nvarchar(500) declare @expression nvarchar(500) declare cur_tmp cursor for select expression,paramvalue from t open cur_tmp fetch next from cur_tmp into @expression,@paramvalue while @@fetch_status=0 begin select @s='if ( '+case when charindex('<',@expression)>0 and charindex('<',@expression)<charindex('@param',@expression) then @value+' between '+left(@expression,charindex('<',@expression)-1)+ ' and '+reverse(left(reverse(@expression),charindex('<',@expression)-1)) when charindex('>',@expression)>0 and charindex('>',@expression)<charindex('@param',@expression) then @value+' between '+reverse(left(reverse(@expression),charindex('<',@expression)-1))+ ' and '+left(@expression,charindex('<',@expression)-1) else replace(@expression,'@param',rtrim(@value)) end +') set @i=1 else set @i=0' exec sp_executesql @s,N'@i tinyint output',@i output if @i=1 goto a fetch next from cur_tmp into @expression,@paramvalue end a: close cur_tmp deallocate cur_tmp GO
因为每次循环都 fetch next from cur_tmp into @expression,@paramvalue,而@paramvalue是output,这样肯定有返回值,即使条件不成立,你的8 > @param > 3格式,应该在取游标后先进行判断,如果该字符串中的 >号多于1个,那么先截取 8 > @param,然后截取 @param >3,再进行判断,根据你说的,还应该考虑 3 < @param < 8这种情况,那么就得再判断 <号,分情况吧,毕竟if(8 > 4 > 3)这种格式在SQL里不成立。
/*3楼写得不错 不过需要改动一下:*/ select @s='select top 1 * from t where '+replace(@expression,'@param',rtrim(@value))+' while @@rowcount > 0 set @i=1 else set @i=0' /*这样就任何的表达式都适用了*/
alter proc p_validexpression(@value int,@paramvalue int output) as declare @i tinyint declare @s nvarchar(500) declare @expression nvarchar(500) declare cur_tmp cursor for select expression,paramvalue from t open cur_tmp fetch next from cur_tmp into @expression,@paramvalue while @@fetch_status=0 begin set @expression=replace(@expression,' ','') select @s='if ( '+case when charindex('<',@expression)>0 and ISNUMERIC(left(@expression,charindex('<',@expression)-1))=1 and ISNUMERIC(reverse(left(reverse(@expression),charindex('<',@expression)-1)))=1 then cast(@value as varchar)+' between '+rtrim(left(@expression,charindex('<',@expression)-1))+ ' and '+reverse(left(reverse(@expression),charindex('<',@expression)-1)) when charindex('>',@expression)>0 and ISNUMERIC(reverse(left(reverse(@expression),charindex('<',@expression)-1)))=1 and ISNUMERIC(left(@expression,charindex('<',@expression)-1))=1 then cast(@value as varchar)+' between '+reverse(left(reverse(@expression),charindex('<',@expression)-1))+ ' and '+left(@expression,charindex('<',@expression)-1) else replace(@expression,'@param',rtrim(@value)) end +') set @i=1 else set @i=0'
exec sp_executesql @s,N'@i tinyint output',@i output if @i=1 goto a fetch next from cur_tmp into @expression,@paramvalue end a: close cur_tmp deallocate cur_tmp GO
/*3楼写得不错 不过需要改动一下:*/ select @s=' set @i=0 select select top 1 * from t where '+replace(@expression,'@param',rtrim(@value))+' if @@rowcount > 0 begin set @i=1 end' /*这样就任何的表达式都适用了*/
/*3楼写得不错 不过需要改动一下:*/ select @s=' set @i=0 select select top 1 * from t where '+replace(@expression,'@param',rtrim(@value))+' if @@rowcount > 0 begin set @i=1 end' /*这样就任何的表达式都适用了*/
用自定义函数,不需要用游标。 create function dbo.istrue(@s varchar(200),@f int) returns int as begin declare @s1 varchar(200),@i int select @s = ltrim(rtrim(@s)) set @i = charindex('@',@s) if @i > 1 begin set @s1= left(@s,@i-1) set @s = ltrim(rtrim(right(@s,len(@s)-@i))) if charindex('>',@s1)> 0 if replace(@s1,'>','') < @f return 1 if charindex('<',@s1)> 0 if replace(@s1,'<','') > @f return 1 end if len(@s)>6 begin set @s1 = right(@s,len(@s)-6) if charindex('>',@s1)> 0 if replace(@s1,'>','') > @f return 1 if charindex('<',@s1)> 0 if replace(@s1,'<','') < @f return 1 end return 0 end go --例子数据 declare @t table(id int,expression varchar(500),paramvalue int) insert into @t select 1,'8 > @param > 3',1 --两个大于 union all select 2,'2 < @param < 3',2 --两个小于 union all select 3,'3 > @param < 8',3 --前大后小 union all select 4,'8 < @param > 5',4 --前小后大 union all select 5,'8 < @param ',5 --前小 union all select 6,'8 > @param ',6--前大 union all select 7,'@param > 5',7 --后大 union all select 8,'@param < 5',8--后小 declare @f int set @f = 5 select id,left(expression,20) expression,paramvalue from @t where dbo.istrue(expression,@f) =0 drop function dbo.istrue /* id expression paramvalue ----------- ---------------------------------------- ----------- 1 8 > @param > 3 1 6 8 > @param 6 7 @param > 5 7 8 @param < 5 8 */
多谢兄弟们的关心和帮助我的问题已经解决code:create proc p_validexpression(@value int) as declare @i tinyint declare @paramvalue int declare @s nvarchar(500) declare @expression nvarchar(500) declare cur_tmp2 cursor for select formulae,vparam from test open cur_tmp2 fetch next from cur_tmp2 into @expression,@paramvalue while @@fetch_status=0 begin select @s='if ('+replace(@expression,'@param',rtrim(@value))+') set @i=1 else set @i=0' exec sp_executesql @s,N'@i tinyint output',@i output if @i=1 begin close cur_tmp2 deallocate cur_tmp2 select @paramvalue return end fetch next from cur_tmp2 into @expression,@paramvalue end set @paramvalue = 0 select @paramvalue close cur_tmp2 deallocate cur_tmp2 GO exec p_validexpression 2 exec p_validexpression 5exec p_validexpression 10 一方面改变了表结构: 在数据库中的存储为: id expression paramvalue 1 @param < 8 and @param > 3 1 2 @param < 3 2 这样,通过上面的存储过程就可以完成这个逻辑了 谢谢大家的关注和帮助
create table t(id int,expression varchar(500),paramvalue int)
insert into t
select 1,'@param > 3',1
union all select 2,'@param < 3',2 create proc p_validexpression(@value int,@paramvalue int output)
as
declare @i tinyint
declare @s nvarchar(500)
declare @expression nvarchar(500)
declare cur_tmp cursor for
select expression,paramvalue from t
open cur_tmp
fetch next from cur_tmp into @expression,@paramvalue
while @@fetch_status=0
begin
select @s='if ('+replace(@expression,'@param',rtrim(@value))+') set @i=1 else set @i=0'
exec sp_executesql @s,N'@i tinyint output',@i output
if @i=1
goto a
fetch next from cur_tmp into @expression,@paramvalue
end
a:
close cur_tmp
deallocate cur_tmp
GOdeclare @i int
exec p_validexpression 4,@i output
print @i
--1declare @i int
exec p_validexpression 2,@i output
print @i
--2
不过我在这里测试的时候发现了些问题:
如果表结构是这个的:
id expression paramvalue
1 8 > @param > 3 1
2 @param < 3 2 那么你的存储过程就不好用了 我自己试着改改可是还是没有成功。希望您能再抽出点时间,给我写下这个存储过程。我的公式主要有三种 大于、小于和在之间。谢谢了
還要對這種關系式處理
as
declare @i tinyint
declare @s nvarchar(500)
declare @expression nvarchar(500)
declare cur_tmp cursor for
select expression,paramvalue from t
open cur_tmp
fetch next from cur_tmp into @expression,@paramvalue
while @@fetch_status=0
begin
select @s='if ( '+case when charindex('<',@expression)>0
and charindex('<',@expression)<charindex('@param',@expression)
then @value+' between '+left(@expression,charindex('<',@expression)-1)+
' and '+reverse(left(reverse(@expression),charindex('<',@expression)-1))
when charindex('>',@expression)>0
and charindex('>',@expression)<charindex('@param',@expression)
then @value+' between '+reverse(left(reverse(@expression),charindex('<',@expression)-1))+
' and '+left(@expression,charindex('<',@expression)-1)
else replace(@expression,'@param',rtrim(@value))
end
+') set @i=1 else set @i=0'
exec sp_executesql @s,N'@i tinyint output',@i output
if @i=1
goto a
fetch next from cur_tmp into @expression,@paramvalue
end
a:
close cur_tmp
deallocate cur_tmp
GO
select @s='select top 1 * from t where '+replace(@expression,'@param',rtrim(@value))+' while @@rowcount > 0 set @i=1 else set @i=0'
/*这样就任何的表达式都适用了*/
as
declare @i tinyint
declare @s nvarchar(500)
declare @expression nvarchar(500)
declare cur_tmp cursor for
select expression,paramvalue from t
open cur_tmp
fetch next from cur_tmp into @expression,@paramvalue
while @@fetch_status=0
begin
set @expression=replace(@expression,' ','')
select @s='if ( '+case when charindex('<',@expression)>0
and ISNUMERIC(left(@expression,charindex('<',@expression)-1))=1
and ISNUMERIC(reverse(left(reverse(@expression),charindex('<',@expression)-1)))=1
then cast(@value as varchar)+' between '+rtrim(left(@expression,charindex('<',@expression)-1))+
' and '+reverse(left(reverse(@expression),charindex('<',@expression)-1))
when charindex('>',@expression)>0
and ISNUMERIC(reverse(left(reverse(@expression),charindex('<',@expression)-1)))=1
and ISNUMERIC(left(@expression,charindex('<',@expression)-1))=1
then cast(@value as varchar)+' between '+reverse(left(reverse(@expression),charindex('<',@expression)-1))+
' and '+left(@expression,charindex('<',@expression)-1)
else replace(@expression,'@param',rtrim(@value))
end
+') set @i=1 else set @i=0'
exec sp_executesql @s,N'@i tinyint output',@i output
if @i=1
goto a
fetch next from cur_tmp into @expression,@paramvalue
end
a:
close cur_tmp
deallocate cur_tmp
GO
select @s=' set @i=0 select select top 1 * from t where '+replace(@expression,'@param',rtrim(@value))+' if @@rowcount > 0 begin set @i=1 end'
/*这样就任何的表达式都适用了*/
select @s=' set @i=0 select select top 1 * from t where '+replace(@expression,'@param',rtrim(@value))+' if @@rowcount > 0 begin set @i=1 end'
/*这样就任何的表达式都适用了*/
create function dbo.istrue(@s varchar(200),@f int)
returns int
as
begin
declare @s1 varchar(200),@i int
select @s = ltrim(rtrim(@s))
set @i = charindex('@',@s)
if @i > 1
begin
set @s1= left(@s,@i-1)
set @s = ltrim(rtrim(right(@s,len(@s)-@i)))
if charindex('>',@s1)> 0 if replace(@s1,'>','') < @f return 1
if charindex('<',@s1)> 0 if replace(@s1,'<','') > @f return 1
end
if len(@s)>6
begin
set @s1 = right(@s,len(@s)-6)
if charindex('>',@s1)> 0 if replace(@s1,'>','') > @f return 1
if charindex('<',@s1)> 0 if replace(@s1,'<','') < @f return 1
end
return 0
end
go
--例子数据
declare @t table(id int,expression varchar(500),paramvalue int)
insert into @t select 1,'8 > @param > 3',1 --两个大于
union all select 2,'2 < @param < 3',2 --两个小于
union all select 3,'3 > @param < 8',3 --前大后小
union all select 4,'8 < @param > 5',4 --前小后大
union all select 5,'8 < @param ',5 --前小
union all select 6,'8 > @param ',6--前大
union all select 7,'@param > 5',7 --后大
union all select 8,'@param < 5',8--后小
declare @f int
set @f = 5
select id,left(expression,20) expression,paramvalue from @t where dbo.istrue(expression,@f) =0
drop function dbo.istrue
/*
id expression paramvalue
----------- ---------------------------------------- -----------
1 8 > @param > 3 1
6 8 > @param 6
7 @param > 5 7
8 @param < 5 8
*/
as
declare @i tinyint
declare @paramvalue int
declare @s nvarchar(500)
declare @expression nvarchar(500)
declare cur_tmp2 cursor for
select formulae,vparam from test
open cur_tmp2
fetch next from cur_tmp2 into @expression,@paramvalue
while @@fetch_status=0
begin
select @s='if ('+replace(@expression,'@param',rtrim(@value))+') set @i=1 else set @i=0'
exec sp_executesql @s,N'@i tinyint output',@i output
if @i=1
begin
close cur_tmp2
deallocate cur_tmp2
select @paramvalue
return
end
fetch next from cur_tmp2 into @expression,@paramvalue
end
set @paramvalue = 0
select @paramvalue
close cur_tmp2
deallocate cur_tmp2
GO
exec p_validexpression 2
exec p_validexpression 5exec p_validexpression 10
一方面改变了表结构:
在数据库中的存储为:
id expression paramvalue
1 @param < 8 and @param > 3 1
2 @param < 3 2
这样,通过上面的存储过程就可以完成这个逻辑了 谢谢大家的关注和帮助