多栏显示.sql

sqlserver 数据库编程的绝好脚本

/*--多栏显示

	通用存储过程,在一条记录中,显示指定条数的记录

--邹建 2004.1--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GO

create proc p_show
@tbname sysname,	--要处理的表名
@col int=2			--一条要显示多少条记录
as
declare @fd varchar(8000),@fds varchar(8000)
	,@id char(38)

select @fd='',@fds=''
select @fds=@fds+',['+name+']'
	,@fd=@fd+',['+name+']'
		+case status 
			when 0x80 then '=cast(['+name+'] as decimal(38,0))'
			else '' end
from syscolumns 
where id=object_id(@tbname)
select @fds=substring(@fds,2,8000)
	,@id='['+cast(newid() as char(36))+']'
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@i int
select @s1='',@s2='',@s3='',@i=0
while @i<@col
	select @s1=@s1+',@'+cast(@i as varchar)
		,@s2=@s2+',@'+cast(@i as varchar)+'='''
			+@fds+' from # where '+@id+'%'
			+cast(@col as varchar)+'='
			+cast(@i as varchar)+''''
		,@s3=@s3+'+''select ''+@'+cast(@i as varchar)
		,@i=@i+1
select @s1=substring(@s1,2,8000)
	,@s2=substring(@s2,2,8000)

print('declare '+@s1+'
select '+@s2+'
print('+@s3+')
')

exec('select '+@id+'=identity(int,0,1)'+@fd+' into # from ['+@tbname+']
select * from # where '+@id+'%2=0
select * from # where '+@id+'%2=1

')
go

--下面是测试

--测试数据
create table 表(name varchar(10),sex bit,age int)
insert into 表
select 'zhang',0,20
union all select 'wang',1,22
union all select 'li',0,30
union all select 'wu',1,18
go

exec p_show '表'
go

--删除测试环境
drop table 表