请问如何给存储过程内的一个函数变量赋予多个固定值
T1内有:
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
a4 b4 c4
a5 b5 c5
a6 b6 c6
....
create proc EXM
@a char(10)
as
select * from T1
where c in (@a)我现在想给@a赋值,也在T1里面取,但是只取c2,c5,c6...这样,请问可以实现吗?求教怎么写。谢谢
T1内有:
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
a4 b4 c4
a5 b5 c5
a6 b6 c6
....
create proc EXM
@a char(10)
as
select * from T1
where c in (@a)我现在想给@a赋值,也在T1里面取,但是只取c2,c5,c6...这样,请问可以实现吗?求教怎么写。谢谢
declare @b varchar(8000)
set @b='select * from t1 where c in ('+@a+')'
exec(@)
set @b='select * from t1 where c in ('''+replace(@a,',',''',''')+''')'
exec(@b)大概是这样
哦另外declare @b varchar(8000)
set @b='select * from t1 where c in ('''+replace(@a,',',''',''')+''')'
@b这么赋值是不行的。sql会把''内的数据作为varchar数据直接输出了。不会执行里面的select
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([A] varchar(2),[B] varchar(2),[C] varchar(2))
insert [tbl]
select 'a1','b1','c1' union all
select 'a2','b2','c2' union all
select 'a3','b3','c3' union all
select 'a4','b4','c4' union all
select 'a5','b5','c5' union all
select 'a6','b6','c6'go
create proc pro_tr @c varchar(20)
as
select * from tbl where CHARINDEX(c,@c)>0exec pro_tr 'c1,c2'
exec pro_tr 'c1c2'/*
A B C
a1 b1 c1
a2 b2 c2
*/不想动态就是用charindex
declare @b varchar(8000)
set @b='select * from t1 where c in ('''+replace(@a,',',''',''')+''')'
exec(@b)这执行不了就把错误信息贴出来
这个有c11就错啦
要这样得改为
CHARINDEX(','+c+',',','+@c+',')
exec exm
服务器: 消息 137,级别 15,状态 1,行 3
必须声明变量 '@a'。
我用的是SQL2000declare @b varchar(8000)
set @b='select * from t1 where c in ('''+replace(@a,',',''',''')+''')'
exec (@b)服务器: 消息 137,级别 15,状态 2,行 2
必须声明变量 '@a'。
declare @b varchar(8000)
declare @a varchar(100)
set @a='c1,c2,c3'
set @b='select * from t1 where c in ('''+replace(@a,',',''',''')+''')'
exec (@b)