-- =============================================
-- Declare and using a KEYSET cursor
-- =============================================
drop proc pro_eachsql
go
create proc pro_eachsql @sqlTo nvarchar(max)
as
begin
create table #table_sql ( /* Temp command storage */
exesql nvarchar(max) COLLATE database_default NULL
)
set nocount on
print 'insert #table_sql '+@sqlTo
exec('insert #table_sql '+@sqlTo)
DECLARE sqllist_cursor CURSOR
KEYSET
FOR select * from #table_sql
DECLARE @sql nvarchar(max)
OPEN sqllist_cursor
FETCH NEXT FROM sqllist_cursor INTO @sql
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
-- PRINT 'add user defined code here'
-- eg.
exec(@sql)
END
FETCH NEXT FROM sqllist_cursor INTO @sql
END
drop table #table_sql
CLOSE sqllist_cursor
DEALLOCATE sqllist_cursor
end
GO
pro_eachsql 'select ''print ''''''+name+'''''''' from sys.tables'
select ''print ''''''+name+'''''''' from sys.tables