转置的存储过程

最新推荐文章于 2021-05-18 07:04:36 发布

蓝天

最新推荐文章于 2021-05-18 07:04:36 发布

阅读量450

点赞数

文章标签：存储 output table insert

本文链接：https://blog.csdn.net/sky_blue/article/details/2005258

版权

create proc proc_sky_blue (@tablename varchar(200))

as

begin

set nocount on

declare @col nvarchar(256)

declare @makesql nvarchar(4000)

declare @insertsql nvarchar(4000)

declare @caculatesql nvarchar(400)

declare @count int

declare @i int

create table #tmp (colname nvarchar(20))

select @caculatesql = 'select @count=count(1) from ' + @tablename

exec sp_executesql @caculatesql, N '@count int output ',@count output

if @count > =1024

begin

raiserror( '表的行数太多了，我转不了 ',16,1)

end

else

begin

select @i=0

while @count > 0

begin

select @i=@i+1

select @makesql = 'alter table #tmp add col '+convert(varchar(20),@i)+ ' int '

exec(@makesql)

select @count=@count-1

end

declare my_cursor cursor for

select name from syscolumns where order by colid

open my_cursor

fetch next from my_cursor into @col

while @@fetch_status = 0

begin

select @makesql = 'select @insertsql= @insertsql + convert(varchar(4), '+@col+ ') + ' ', ' ' from ' +@tablename

select @insertsql =N 'insert #tmp values ( ' ' '+@col+ ' ' ', '

execute sp_executesql @makesql,N '@insertsql nvarchar(4000) output ' ,@insertsql output

select @insertsql = left(@insertsql,len(@insertsql)-1) + ') '

exec(@insertsql)

fetch next from my_cursor into @col

end

close my_cursor

deallocate my_cursor

select * from #tmp

set nocount off

end

end