declare cur cursor for select id,sql from view1 open cur declare @id int ,@sql varchar(100) fetch next from cur into @id,@sql while @@fetch_status=0 begin print @id,@sql --做你的事情 fetch next from cur into @id,@sql end close cur deallocate cur
本人想建一个存储过程,该存储过程的功能是从一个view里面选取一定的字段 select ID,SQL from view1 会得到一个数据集。我想把这个数据集的sQl字段里面的这个sql语句逐一的执行一遍 由于本人对游标了解甚少,请各位多多帮助! 谢谢了先,重分感谢----------- 直接case when 就可以,不必这么麻烦
DECLARE Employee_Cursor CURSOR FOR SELECT LastName, FirstName FROM Northwind.dbo.Employees--声明游标 OPEN Employee_Cursor--打开游标 FETCH NEXT FROM Employee_Cursor WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Employee_Cursor--返回被 FETCH 语句执行的最后游标的状态 END CLOSE Employee_Cursor--关闭游标 DEALLOCATE Employee_Cursor--删除游标引用
create proc proc_exec as begin declare cursor_tb cursor for select ID,SQL from view1 declare @ID varchar(100),@SQL varchar(8000) open cursor_tb fetch next from cursor_tb into @ID, @SQL while @@FETCH_STATUS = 0 begin exec(@SQL) fetch next from cursor_tb into @ID, @SQL end close cursor_tb deallocate cursor_tb end
select id,sql from view1
open cur
declare @id int ,@sql varchar(100)
fetch next from cur into @id,@sql
while @@fetch_status=0
begin
print @id,@sql --做你的事情
fetch next from cur into @id,@sql
end
close cur
deallocate cur
select ID,SQL from view1
会得到一个数据集。我想把这个数据集的sQl字段里面的这个sql语句逐一的执行一遍
由于本人对游标了解甚少,请各位多多帮助!
谢谢了先,重分感谢----------- 直接case when 就可以,不必这么麻烦
SELECT LastName, FirstName FROM Northwind.dbo.Employees--声明游标
OPEN Employee_Cursor--打开游标
FETCH NEXT FROM Employee_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor--返回被 FETCH 语句执行的最后游标的状态
END
CLOSE Employee_Cursor--关闭游标
DEALLOCATE Employee_Cursor--删除游标引用
create proc proc_exec
as
begin
declare cursor_tb cursor for
select ID,SQL from view1
declare @ID varchar(100),@SQL varchar(8000)
open cursor_tb
fetch next from cursor_tb
into @ID, @SQL
while @@FETCH_STATUS = 0
begin
exec(@SQL)
fetch next from cursor_tb
into @ID, @SQL
end
close cursor_tb
deallocate cursor_tb
end