sqlserver 游标的实现

alter PROCEDURE [dbo].[sp_copyTables] 
@sqlWhere varchar(100),
@newDBName  varchar(50)
as
begin
declare @sql nvarchar(500),@insert varchar(1000), @tableName varchar(50)
 
--定义游标
set @sql=‘declare getTablesNames cursor for select Name from sysobjects where  XType=‘‘U‘‘ and ‘+@sqlWhere 
--执行游标 
 exec (@sql)   
--打开
open getTablesNames   
--取第一个数
fetch next from getTablesNames into @tableName
--遍历游标
while @@fetch_status <> -1
begin
set @insert=‘insert into ‘+@newDBName+‘.dbo.‘+@tableName+‘ select *  from ‘+@tableName
print @insert
--exec(@insert)
fetch next from getTablesNames into @tableName
end
--关闭游标
close getTablesNames
--回收游标
deallocate getTablesNames
end

执行存储过程

exec [sp_copyTables] ‘Name not in (‘‘dtproperties‘‘,‘‘oSensorData‘‘,‘‘oSands‘‘)‘, ‘jarlinfowkk2013‘

参数中间要用两个单引号来表示‘

sqlserver 游标的实现,布布扣,bubuko.com

原文：http://blog.csdn.net/kevindr/article/details/21943075