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’
参数中间要用两个单引号来表示’
