小试一段,没有环境测试,先 declare @in int,@out int set @in=8 set @out=0if exists(select 1 from tb where isnumic(re)=0 and re=@in) begin select @out=nl from tb where re=@in end if exists(select 1 from tb where charindex('$',re)>0) begin select @out=nl from tb where @in between cast(left(re,charindex('$',re)-1) as int) and cast(right(re,len(re)-charindex('$',re)) as int)
create table tb(re varchar(10),st varchar(10),lo varchar(10),ob varchar(10),gr varchar(10),vi varchar(10),nl int) insert into tb values('1' , '1$12' ,'1$7' ,'-' , '1$8' ,'1$7' ,1) insert into tb values('2' , '13$15' ,'8$11' ,'-' , '9$11' ,'8$12' ,2) insert into tb values('3' , '16$18' ,'12$16' ,'-' , '12$14' ,'13$16' ,3) insert into tb values('4' , '19$21' ,'17$22' ,'-' , '15$19' ,'17$19' ,4) insert into tb values('5$6' , '22$24' ,'23$27' ,'-' , '20$24' ,'20$22' ,5 ) insert into tb values('7$10' , '25$26' ,'28$33' ,'-' , '25$28' ,'23$28' ,6 ) insert into tb values('11$12', '27$29' ,'34$39' ,'-' , '29$31' ,'29$34' ,7 ) insert into tb values('13' , '30$31' ,'40$47' ,'-' , '32$33' ,'35$39' ,8 ) insert into tb values('14' , '32$33' ,'48$54' ,'-' , '34$35' ,'40$45' ,9 ) insert into tb values('15' , '34$35' ,'55$60' ,'-' , '36' ,'46$52' ,10 ) insert into tb values('16' , '36' ,'61$64' ,'-' , '37' ,'53$58' ,11 ) insert into tb values('-' , '-' ,'65$68' ,'1$4', '38' ,'59$64' ,12 ) insert into tb values('-' , '-' ,'69$71' ,'5$6', '39' ,'65$68' ,13 ) insert into tb values('-' , '37' ,'72$76' ,'7' , '40' ,'69$72' ,14 ) insert into tb values('-' , '-' ,'77$80' ,'8$9', '41' ,'73$75' ,15 ) go create procedure my_proc @val int , @return int OUTPUT as begin if exists(select 1 from tb where (isnumeric(re) = 1 and cast(re as int) = @val) or (charindex('$' , re) > 0 and @val between cast(left(re , charindex('$' , re) - 1) as int) and cast(substring(re , charindex('$' , re) + 1 , len(re)) as int))) set @return = (select top 1 nl from tb where (isnumeric(re) = 1 and cast(re as int) = @val) or (charindex('$' , re) > 0 and @val between cast(left(re , charindex('$' , re) - 1) as int) and cast(substring(re , charindex('$' , re) + 1 , len(re)) as int))) else if exists(select 1 from tb where (isnumeric(st) = 1 and cast(st as int) = @val) or (charindex('$' , st) > 0 and @val between cast(left(st , charindex('$' , st) - 1) as int) and cast(substring(st , charindex('$' , st) + 1 , len(st)) as int))) set @return = (select top 1 nl from tb where (isnumeric(st) = 1 and cast(st as int) = @val) or (charindex('$' , st) > 0 and @val between cast(left(st , charindex('$' , st) - 1) as int) and cast(substring(st , charindex('$' , st) + 1 , len(st)) as int))) else if exists(select 1 from tb where (isnumeric(lo) = 1 and cast(lo as int) = @val) or (charindex('$' , lo) > 0 and @val between cast(left(lo , charindex('$' , lo) - 1) as int) and cast(substring(lo , charindex('$' , lo) + 1 , len(lo)) as int))) set @return = (select top 1 nl from tb where (isnumeric(lo) = 1 and cast(lo as int) = @val) or (charindex('$' , lo) > 0 and @val between cast(left(lo , charindex('$' , lo) - 1) as int) and cast(substring(lo , charindex('$' , lo) + 1 , len(lo)) as int))) else if exists(select 1 from tb where (isnumeric(ob) = 1 and cast(ob as int) = @val) or (charindex('$' , ob) > 0 and @val between cast(left(ob , charindex('$' , ob) - 1) as int) and cast(substring(ob , charindex('$' , ob) + 1 , len(ob)) as int))) set @return = (select top 1 nl from tb where (isnumeric(ob) = 1 and cast(ob as int) = @val) or (charindex('$' , ob) > 0 and @val between cast(left(ob , charindex('$' , ob) - 1) as int) and cast(substring(ob , charindex('$' , ob) + 1 , len(ob)) as int))) else if exists(select 1 from tb where (isnumeric(gr) = 1 and cast(gr as int) = @val) or (charindex('$' , gr) > 0 and @val between cast(left(gr , charindex('$' , gr) - 1) as int) and cast(substring(gr , charindex('$' , gr) + 1 , len(gr)) as int))) set @return = (select top 1 nl from tb where (isnumeric(gr) = 1 and cast(gr as int) = @val) or (charindex('$' , gr) > 0 and @val between cast(left(gr , charindex('$' , gr) - 1) as int) and cast(substring(gr , charindex('$' , gr) + 1 , len(gr)) as int))) else if exists(select 1 from tb where (isnumeric(vi) = 1 and cast(vi as int) = @val) or (charindex('$' , vi) > 0 and @val between cast(left(vi , charindex('$' , vi) - 1) as int) and cast(substring(vi , charindex('$' , vi) + 1 , len(vi)) as int))) set @return = (select top 1 nl from tb where (isnumeric(vi) = 1 and cast(vi as int) = @val) or (charindex('$' , vi) > 0 and @val between cast(left(vi , charindex('$' , vi) - 1) as int) and cast(substring(vi , charindex('$' , vi) + 1 , len(vi)) as int)))end goDECLARE @re as intexec my_proc 3 , @re OUTPUT select @re /*
declare @in int,@out int
set @in=8
set @out=0if exists(select 1 from tb where isnumic(re)=0 and re=@in)
begin
select
@out=nl
from tb
where re=@in
end
if exists(select 1 from tb where charindex('$',re)>0)
begin
select
@out=nl
from tb
where @in between cast(left(re,charindex('$',re)-1) as int) and cast(right(re,len(re)-charindex('$',re)) as int)
insert into tb values('1' , '1$12' ,'1$7' ,'-' , '1$8' ,'1$7' ,1)
insert into tb values('2' , '13$15' ,'8$11' ,'-' , '9$11' ,'8$12' ,2)
insert into tb values('3' , '16$18' ,'12$16' ,'-' , '12$14' ,'13$16' ,3)
insert into tb values('4' , '19$21' ,'17$22' ,'-' , '15$19' ,'17$19' ,4)
insert into tb values('5$6' , '22$24' ,'23$27' ,'-' , '20$24' ,'20$22' ,5 )
insert into tb values('7$10' , '25$26' ,'28$33' ,'-' , '25$28' ,'23$28' ,6 )
insert into tb values('11$12', '27$29' ,'34$39' ,'-' , '29$31' ,'29$34' ,7 )
insert into tb values('13' , '30$31' ,'40$47' ,'-' , '32$33' ,'35$39' ,8 )
insert into tb values('14' , '32$33' ,'48$54' ,'-' , '34$35' ,'40$45' ,9 )
insert into tb values('15' , '34$35' ,'55$60' ,'-' , '36' ,'46$52' ,10 )
insert into tb values('16' , '36' ,'61$64' ,'-' , '37' ,'53$58' ,11 )
insert into tb values('-' , '-' ,'65$68' ,'1$4', '38' ,'59$64' ,12 )
insert into tb values('-' , '-' ,'69$71' ,'5$6', '39' ,'65$68' ,13 )
insert into tb values('-' , '37' ,'72$76' ,'7' , '40' ,'69$72' ,14 )
insert into tb values('-' , '-' ,'77$80' ,'8$9', '41' ,'73$75' ,15 )
go
create procedure my_proc @val int , @return int OUTPUT
as
begin
if exists(select 1 from tb where (isnumeric(re) = 1 and cast(re as int) = @val) or (charindex('$' , re) > 0 and @val between cast(left(re , charindex('$' , re) - 1) as int) and cast(substring(re , charindex('$' , re) + 1 , len(re)) as int)))
set @return = (select top 1 nl from tb where (isnumeric(re) = 1 and cast(re as int) = @val) or (charindex('$' , re) > 0 and @val between cast(left(re , charindex('$' , re) - 1) as int) and cast(substring(re , charindex('$' , re) + 1 , len(re)) as int)))
else
if exists(select 1 from tb where (isnumeric(st) = 1 and cast(st as int) = @val) or (charindex('$' , st) > 0 and @val between cast(left(st , charindex('$' , st) - 1) as int) and cast(substring(st , charindex('$' , st) + 1 , len(st)) as int)))
set @return = (select top 1 nl from tb where (isnumeric(st) = 1 and cast(st as int) = @val) or (charindex('$' , st) > 0 and @val between cast(left(st , charindex('$' , st) - 1) as int) and cast(substring(st , charindex('$' , st) + 1 , len(st)) as int)))
else
if exists(select 1 from tb where (isnumeric(lo) = 1 and cast(lo as int) = @val) or (charindex('$' , lo) > 0 and @val between cast(left(lo , charindex('$' , lo) - 1) as int) and cast(substring(lo , charindex('$' , lo) + 1 , len(lo)) as int)))
set @return = (select top 1 nl from tb where (isnumeric(lo) = 1 and cast(lo as int) = @val) or (charindex('$' , lo) > 0 and @val between cast(left(lo , charindex('$' , lo) - 1) as int) and cast(substring(lo , charindex('$' , lo) + 1 , len(lo)) as int)))
else
if exists(select 1 from tb where (isnumeric(ob) = 1 and cast(ob as int) = @val) or (charindex('$' , ob) > 0 and @val between cast(left(ob , charindex('$' , ob) - 1) as int) and cast(substring(ob , charindex('$' , ob) + 1 , len(ob)) as int)))
set @return = (select top 1 nl from tb where (isnumeric(ob) = 1 and cast(ob as int) = @val) or (charindex('$' , ob) > 0 and @val between cast(left(ob , charindex('$' , ob) - 1) as int) and cast(substring(ob , charindex('$' , ob) + 1 , len(ob)) as int)))
else
if exists(select 1 from tb where (isnumeric(gr) = 1 and cast(gr as int) = @val) or (charindex('$' , gr) > 0 and @val between cast(left(gr , charindex('$' , gr) - 1) as int) and cast(substring(gr , charindex('$' , gr) + 1 , len(gr)) as int)))
set @return = (select top 1 nl from tb where (isnumeric(gr) = 1 and cast(gr as int) = @val) or (charindex('$' , gr) > 0 and @val between cast(left(gr , charindex('$' , gr) - 1) as int) and cast(substring(gr , charindex('$' , gr) + 1 , len(gr)) as int)))
else
if exists(select 1 from tb where (isnumeric(vi) = 1 and cast(vi as int) = @val) or (charindex('$' , vi) > 0 and @val between cast(left(vi , charindex('$' , vi) - 1) as int) and cast(substring(vi , charindex('$' , vi) + 1 , len(vi)) as int)))
set @return = (select top 1 nl from tb where (isnumeric(vi) = 1 and cast(vi as int) = @val) or (charindex('$' , vi) > 0 and @val between cast(left(vi , charindex('$' , vi) - 1) as int) and cast(substring(vi , charindex('$' , vi) + 1 , len(vi)) as int)))end
goDECLARE @re as intexec my_proc 3 , @re OUTPUT
select @re
/*
-----------
3(所影响的行数为 1 行)
*/exec my_proc 5 , @re OUTPUT
select @re
/*
-----------
5(所影响的行数为 1 行)
*/exec my_proc 7 , @re OUTPUT
select @re
/*
-----------
6(所影响的行数为 1 行)
*/exec my_proc 23 , @re OUTPUT
select @re
/*
-----------
5(所影响的行数为 1 行)
*/exec my_proc 77 , @re OUTPUT
select @re
/*
-----------
15(所影响的行数为 1 行)
*/drop table tb
drop procedure my_proc