表book.c1是INT类型,有如下查询:if exists(select * from booking where c1 in(1,2,11,12,15,16))
print '满足条件'
上面语句执行没有问题,如果in里面的内容是由参数传递的,该如何写??问题焦点: 因为C1是INT型,所以采用动态的编写,这个数据类型如何解决???还有不能用charindex()函数,因为INT型的1和11都会满足条件,所以必须用IN
帮忙写成一个过程吧,in里的内容是参数传递的,而且,该过程必须有返回值。我先简单写一下范例,即我想要的效果:create procedure ddr
@c1list varchar(8000),
@返回值 int out
as
begin
if exists(select * from booking where c1 in (@c1list))
begin
print '满足条件'
set @返回值 = 1
end
else
begin
print '不满足条件'
set @返回值 = 2
end
end
go上面的过程只是表达我所需要的结果,书写不能满足语法条件的。所以希望大家帮我改改。
print '满足条件'
上面语句执行没有问题,如果in里面的内容是由参数传递的,该如何写??问题焦点: 因为C1是INT型,所以采用动态的编写,这个数据类型如何解决???还有不能用charindex()函数,因为INT型的1和11都会满足条件,所以必须用IN
帮忙写成一个过程吧,in里的内容是参数传递的,而且,该过程必须有返回值。我先简单写一下范例,即我想要的效果:create procedure ddr
@c1list varchar(8000),
@返回值 int out
as
begin
if exists(select * from booking where c1 in (@c1list))
begin
print '满足条件'
set @返回值 = 1
end
else
begin
print '不满足条件'
set @返回值 = 2
end
end
go上面的过程只是表达我所需要的结果,书写不能满足语法条件的。所以希望大家帮我改改。
1,2,3,4还是
'1,2,3,4'
@c1list varchar(8000),
@返回值 int out
as
begin
if exists(select * from booking where charindex(','+rtrim(c1)+',' , ','+@c1list+',')>0)
begin
print '满足条件'
set @返回值 = 1
end
else
begin
print '不满足条件'
set @返回值 = 2
end
end
go
if exists(select * from booking where charindex(','+ltrim(c1)+',',@c1list)>0)
if exists(select * from booking where charindex(','+ltrim(c1)+',',','+@c1list+',')>0)
@c1list varchar(8000),
@返回值 int out
as
begin
if exists(select * from booking where CHARINDEX(','+CAST(c1 AS VARCHAR(8))+',',','+@c1list+',')>0)
begin
print '满足条件'
set @返回值 = 1
end
else
begin
print '不满足条件'
set @返回值 = 2
end
end
go
alter procedure ddr
@c1list varchar(8000),
@返回值 int out
as
begin
create table #(c1 int)
insert into # exec ('select c1 from booking where c1 in ('+ @c1list +')')
if (select count(*) from #)>0
begin
print '满足条件'
set @返回值 = 1
end
else
begin
print '不满足条件'
set @返回值 = 2
end
end
print @返回值
go
create procedure ddr
@c1list varchar(8000),
@返回值 int out
as
begin
exec('select * into ##t from booking where c1 in ('+@c1list+')')
if exists(select * from ##t)
begin
print '满足条件'
set @返回值 = 1
end
else
begin
print '不满足条件'
set @返回值 = 2
end
drop table ##t
end
go
exec('select * from booking where c1 in ('+@c1list+')')
if @@rowcount <> 0
BEGIN
.....
END