现在需要写一个存储过程
如:
create table user_info
(
uid int identity(1,1) primary key,
uname varchar(50)
)
goinsert into user_info values('张三')
insert into user_info values('李四')
insert into user_info values('王五')
insert into user_info values('赵六')
insert into user_info values('陈七')
go
create proc sp_operatordata
@ids
as
declare @sql varchar(4000)
--省略部分
go
我需要达到的目的,当执行这个存储过程,传入参数(1,3,5) 然后在存储过程里面查询出结果,
我需要遍历操作这个结果集,然后通过判断取出我想要的数据,最后执行其它的语句
如:存储过程中查询出uid为1,3,5的用户,然后操作查询结果,将uid为3、5的用户过滤出来
如:
create table user_info
(
uid int identity(1,1) primary key,
uname varchar(50)
)
goinsert into user_info values('张三')
insert into user_info values('李四')
insert into user_info values('王五')
insert into user_info values('赵六')
insert into user_info values('陈七')
go
create proc sp_operatordata
@ids
as
declare @sql varchar(4000)
--省略部分
go
我需要达到的目的,当执行这个存储过程,传入参数(1,3,5) 然后在存储过程里面查询出结果,
我需要遍历操作这个结果集,然后通过判断取出我想要的数据,最后执行其它的语句
如:存储过程中查询出uid为1,3,5的用户,然后操作查询结果,将uid为3、5的用户过滤出来
set @sql ='1,3,5'
select * from user_info
where charindex(','+uid+',',','+@sql+',')>0
不是这样的,可能是我描述有歧义
比如 set @sql = 'select * from user_info where uid in (' + ids + ')'然后执行这条语句,会得到一个结果集,我如何操作这个结果集
(
uid int identity(1,1) primary key,
uname varchar(50)
)
goinsert into user_info values('张三')
insert into user_info values('李四')
insert into user_info values('王五')
insert into user_info values('赵六')
insert into user_info values('陈七')
go
create proc sp_operatordata
@ids
as
declare @sql varchar(4000)
select * from user_info where @sql like '%'+uname+'%'
go
@ids varchar(100)
as
select * from user_info
where charindex(','+uid+',',','+@ids+',')>0
go--or
create proc sp_operatordata
@ids varchar(100)
as
exec('select * from user_info
where uid in('+@ids+')')
go
insert #temp exec ...
CREATE FUNCTION proc_Test(strID AS VARCHAR(20))
RETURNS ##TMP TABLE(....) -----全局定义临时表的表结构
AS
BEGIN
DECLARE @SQL AS VARCHAR(4000)
SET @SQL='SELECT * INTO ##TMP FROM TB WHERE ID IN (' +strID +')'
EXEC @SQL
RETURN ##TMP
END
(
uid int identity(1,1) primary key,
uname varchar(50)
)
goinsert into user_info values('张三')
insert into user_info values('李四')
insert into user_info values('王五')
insert into user_info values('赵六')
insert into user_info values('陈七')
gocreate proc sp_operatordata
@sql varchar(4000)
as
begin
select * from user_info where @sql like '%'+uname+'%'
endexec sp_operatordata '王五,赵六'
/*结果*/
uid nname
3 王五
4 赵六
@ids varchar(100)
as
begin
select * from user_info where @ids like '%'+convert(varchar(10),uid)+'%';
end
go