--那就写类似这样的存储过程就行了 create proc p_qry @userlist varchar(8000) as set nocount on if @userlist='[All]' select * from [users] else exec('select * from users where userid in ('+@userlist+')')
最好是写成循环在程序中控制,写一个存储过程,循环中每个id调用一次存储过程完成你的工作, 要是用select * from users where userid in (@P)的写法,@P的值过多的时候很容易引起sql语句超长无法执行的情况.
我上面说过多个条件不行啊,如果有两个条件,一个是userid, 一个是username,这样我有两个参数,那不是有四种组合了,所以用if 不行,exec( 'select * from users where userid in ('+@userlist+') and username in (' + @usernamelist + ')' [All]怎么弄呢
create proc p_qry @uid_list varchar(4000), @uname_list varchar(4000) as set nocount on declare @s varchar(8000) select @s =case when isnull(@uid_list,'') in('','[ALL]') then '' else ' and userid in('+@uid_list+')' end +case when isnull(@uname_list,'') in('','[ALL]') then '' else ' and username in('+@uname_list+')' end ,@s=case when @s='' then '' else 'where '+stuff(@s,1,4,'') end exec('select * from [users] '+@s)
大哥,应该还有一个问题,我这查出的只是中间结果 我能不能把它们放在临时表里? exec('select * into #Temp from [users] '+@s)执行完后就select * from #Temp 查不到
我看有人有过exists好像比较简洁,可好难写
--那你就直接写吧,喜欢怎么处理都行,费事考虑临时表的作用域或者多用户的冲突问题 --参数传递的方式改一下,用 user001,user002 的格式,即不要' select * from [users] where (isnull(@uid_list,'') in('','[ALL]') or charindex(','+cast(userid as varchar)+',',','+@uid_list+',')>0) and (isnull(@uname_list,'') in('','[ALL]') or charindex(','+username+',',','+@uname_list+',')>0)
exists的写法不知道是怎么回事,帖出来看看.
select * from users where (userid in (@P) or not exists(select 1 from userid where userid in (@P1)) (username in (@P2) or not exists(select 1 from users where username in (@P2))
--测试数据 create table users(userid varchar(10)) insert users select '01' union all select '02' union all select '03' union all select '04' go--查询 declare @p1 varchar(100) set @p1='''01'',''02'',''03'''select * from users where (userid in (@P1) or not exists(select 1 from users where userid in (@P1))) /*--测试结果userid ---------- 01 02 03 04(所影响的行数为 4 行) --*/ go--删除测试 drop table users
你的不对应该是这样 create table users(userid varchar(10)) insert users select '01' union all select '02' union all select '03' union all select '04' go--查询 --declare @p1 varchar(100) --set @p1='''01'',''02'',''03'''select * from users where (userid in ('01','02','03') or not exists(select 1 from users where userid in ('01','02','03'))) /*--测试结果userid ---------- 01 02 03 (所影响的行数为 3 行) --*/ go--删除测试 drop table users
可这个写法有个坏处 select * from users where (userid in ('05') or not exists(select 1 from users where userid in ('05'))) 它也返回所有
嘿嘿,搞定了 select * from users where (userid in ('01', '02', '03') or (not exists(select 1 from users where userid in ('01', '02', '03'))) and userid = '[all]')
哎呀,这个又不行了 select * from users where (userid in ('[all]') or (not exists(select 1 from users where userid in ('[all]'))) and userid = '[all]')
我真是觉得奇怪,那个exists有意义吗? 下面这个查询结果与上面的那个有差别吗?select * from users where (userid in ('01', '02', '03') or userid = '[all]')
'你不是要在前台拼的吗? p="'01','02','03'" sql="select * from users where userid in (" +p+") or "+p+"='[all]'"p="'[all]'" sql="select * from users where userid in (" +p+") or "+p+"='[all]'"
是我弄错了,这样写就全对了 select * from users where (userid in ('01', '02', '03') or (not exists(select 1 from users where userid in ('01', '02', '03'))) and '''01'', ''02'', ''03''' = '[all]') --(所影响的行数为 3 行) select * from users where (userid in ('01', '02', '05') or (not exists(select 1 from users where userid in ('01', '02', '05'))) and '''01'', ''02'', ''05''' = '[all]') (所影响的行数为 2 行) select * from users where (userid in ('05') or (not exists(select 1 from users where userid in ('05'))) and '05' = '[all]') (所影响的行数为 0 行)select * from users where (userid in ('[all]') or (not exists(select 1 from users where userid in ('[all]'))) and '[all]' = '[all]') (所影响的行数为 4 行)可以这些参数怎么传啊,该死的引号和逗号 select * from users where (userid in (@P) or (not exists(select 1 from users where userid in (@P))) and @P = '[all]')
set @P='aaa,bbb,ccc....'exec ('select * from users where userid in ('+@P+') ')
create proc p_qry
@userlist varchar(8000)
as
set nocount on
if @userlist='[All]'
select * from [users]
else
exec('select * from users where userid in ('+@userlist+')')
要是用select * from users where userid in (@P)的写法,@P的值过多的时候很容易引起sql语句超长无法执行的情况.
'select * from users where userid in ('+@userlist+') and username in (' + @usernamelist + ')'
[All]怎么弄呢
@uid_list varchar(4000),
@uname_list varchar(4000)
as
set nocount on
declare @s varchar(8000)
select @s
=case
when isnull(@uid_list,'') in('','[ALL]')
then ''
else ' and userid in('+@uid_list+')'
end
+case
when isnull(@uname_list,'') in('','[ALL]')
then ''
else ' and username in('+@uname_list+')'
end
,@s=case when @s='' then '' else 'where '+stuff(@s,1,4,'') end
exec('select * from [users] '+@s)
我能不能把它们放在临时表里?
exec('select * into #Temp from [users] '+@s)执行完后就select * from #Temp 查不到
--参数传递的方式改一下,用 user001,user002 的格式,即不要'
select * from [users]
where
(isnull(@uid_list,'') in('','[ALL]')
or charindex(','+cast(userid as varchar)+',',','+@uid_list+',')>0)
and
(isnull(@uname_list,'') in('','[ALL]')
or charindex(','+username+',',','+@uname_list+',')>0)
where
(userid in (@P) or not exists(select 1 from userid where userid in (@P1))
(username in (@P2) or not exists(select 1 from users where username in (@P2))
create table users(userid varchar(10))
insert users select '01'
union all select '02'
union all select '03'
union all select '04'
go--查询
declare @p1 varchar(100)
set @p1='''01'',''02'',''03'''select * from users
where
(userid in (@P1) or not exists(select 1 from users where userid in (@P1)))
/*--测试结果userid
----------
01
02
03
04(所影响的行数为 4 行)
--*/
go--删除测试
drop table users
create table users(userid varchar(10))
insert users select '01'
union all select '02'
union all select '03'
union all select '04'
go--查询
--declare @p1 varchar(100)
--set @p1='''01'',''02'',''03'''select * from users
where
(userid in ('01','02','03') or not exists(select 1 from users where userid in ('01','02','03')))
/*--测试结果userid
----------
01
02
03
(所影响的行数为 3 行)
--*/
go--删除测试
drop table users
select * from users
where
(userid in ('05') or not exists(select 1 from users where userid in ('05')))
它也返回所有
select * from users
where
(userid in ('01', '02', '03') or (not exists(select 1 from users where userid in ('01', '02', '03'))) and userid = '[all]')
select * from users
where
(userid in ('[all]') or (not exists(select 1 from users where userid in ('[all]'))) and userid = '[all]')
where
(userid in ('01', '02', '03') or userid = '[all]')
sql="select * from users where userid in (" +p+") or "+p+"='[all]'"p="'[all]'"
sql="select * from users where userid in (" +p+") or "+p+"='[all]'"
select * from users
where
(userid in ('01', '02', '03') or (not exists(select 1 from users where userid in ('01', '02', '03'))) and '''01'', ''02'', ''03''' = '[all]')
--(所影响的行数为 3 行)
select * from users
where
(userid in ('01', '02', '05') or (not exists(select 1 from users where userid in ('01', '02', '05'))) and '''01'', ''02'', ''05''' = '[all]')
(所影响的行数为 2 行)
select * from users
where
(userid in ('05') or (not exists(select 1 from users where userid in ('05'))) and '05' = '[all]')
(所影响的行数为 0 行)select * from users
where
(userid in ('[all]') or (not exists(select 1 from users where userid in ('[all]'))) and '[all]' = '[all]')
(所影响的行数为 4 行)可以这些参数怎么传啊,该死的引号和逗号
select * from users
where
(userid in (@P) or (not exists(select 1 from users where userid in (@P))) and @P = '[all]')