alter procedure TestTable(@tableName nvarchar(20))
As
declare @PartName nvarchar(100)
declare @cursor cursor
exec('set @cursor = cursor for select top 10 Desc_ from ' + @tableName)
open @cursor
fetch next from @cursor into @PartName
while(@@fetch_status=0)
begin
print @PartName
fetch next from @cursor into @PartName
end
close @cursor
deallocate @cursor
As
declare @PartName nvarchar(100)
declare @cursor cursor
exec('set @cursor = cursor for select top 10 Desc_ from ' + @tableName)
open @cursor
fetch next from @cursor into @PartName
while(@@fetch_status=0)
begin
print @PartName
fetch next from @cursor into @PartName
end
close @cursor
deallocate @cursor
declare @cursor cursor
exec('set @cursor = cursor for select top 10 Desc_ from ' + @tableName)这部分
as
begin
declare @PartName nvarchar(100),@str varchar(max)
set @st='declare @cursor cursor for select top 10 Desc_ from '+@tableName+
'open @cursor
fetch next from @cursor into '+@PartName+
'while(@@fetch_status=0)
begin
print @PartName
fetch next from @cursor into '+@PartName+
'end
close @cursor
deallocate @cursor'
exec(@str)end
As
exec ('
declare @PartName nvarchar(max)
select top 10 @PartName = isnull(@PartName + '
','') + Desc_ from ' + @tableName + '
print @PartName')
As
exec ('
declare @PartName nvarchar(max)
select top 10 @PartName = isnull(@PartName + ''
'','''') + Desc_ from ' + @tableName + '
print @PartName')
CREATE TABLE TB(Desc_ int)
insert TB
select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all select 12
GO
--SQL:
CREATE PROCEDURE Testtable (@tableName NVARCHAR(20))
AS
DECLARE @PartName NVARCHAR(100) --注意,动态SQL中定义的游标在当前作用域是可见的. 但动态SQL外的变量在动态SQL中是不可见的
EXEC('DECLARE my_cursor CURSOR for select top 10 Desc_ from ' + @tableName) OPEN my_cursor FETCH NEXT FROM my_cursor INTO @PartName WHILE( @@FETCH_STATUS = 0 )
BEGIN
PRINT @PartName FETCH NEXT FROM my_cursor INTO @PartName
END CLOSE my_cursor DEALLOCATE my_cursor
goEXEC Testtable N'TB'
/*
1
2
3
4
5
6
7
8
9
10
*/