select identity(int,1,1) as ids, name into #tt from syscolumns where id = (select id from sysobjects where name = 'TestLiuCan' and type = 'u') select * from #tt(5 行受影响) ids name ----------- -------------------------------------------------------------------------------------------------------------------------------- 1 id 2 c1 3 c2 4 c3 5 c4(5 行受影响) 我开始想用这个,但不行啊,期待高手!!
已经解决问题: CREATE TABLE [dbo].[TestLiuCan]( [id] [int] IDENTITY(1,1) NOT NULL, [c1] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [c2] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [c3] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL, [c4] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]--功能:根据列ID来获取表中的数据 --作者:火鸟 --日期:2008-4-28 --测试用:getSqlByColIds 'TestLiuCan','1,2,3' --结果 /* id c1 c2 ----------- -------------------- -------------------- 1 a a 2 b b 3 c c 4 d d(4 行受影响) */ set nocount on create procedure getSqlByColIds ( @tableName nvarchar(20), @cols nvarchar(40) ) as begindeclare @sql nvarchar(200) --保存整个sql语句 declare @i int --取列ID字符串里的位置set @sql = ' select ' set @i = charindex(',',@cols) while @i>=1 begin set @sql = @sql + col_name(object_id(@tableName),convert(int,left(@cols,@i-1))) + ',' set @cols = substring(@cols,@i+1,len(@cols)) set @i = charindex(',',@cols) end if @cols <> '\' set @sql = @sql + col_name(object_id(@tableName),convert(int,@cols))set @sql = @sql + ' from ' + @tableName exec(@sql)end
where id = (select id from sysobjects where name = 'TestLiuCan' and type = 'u') select * from #tt(5 行受影响)
ids name
----------- --------------------------------------------------------------------------------------------------------------------------------
1 id
2 c1
3 c2
4 c3
5 c4(5 行受影响)
我开始想用这个,但不行啊,期待高手!!
CREATE TABLE [dbo].[TestLiuCan](
[id] [int] IDENTITY(1,1) NOT NULL,
[c1] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[c2] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[c3] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[c4] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]--功能:根据列ID来获取表中的数据
--作者:火鸟
--日期:2008-4-28
--测试用:getSqlByColIds 'TestLiuCan','1,2,3'
--结果
/*
id c1 c2
----------- -------------------- --------------------
1 a a
2 b b
3 c c
4 d d(4 行受影响)
*/
set nocount on
create procedure getSqlByColIds
(
@tableName nvarchar(20),
@cols nvarchar(40)
)
as
begindeclare @sql nvarchar(200) --保存整个sql语句
declare @i int --取列ID字符串里的位置set @sql = ' select '
set @i = charindex(',',@cols)
while @i>=1
begin
set @sql = @sql + col_name(object_id(@tableName),convert(int,left(@cols,@i-1))) + ','
set @cols = substring(@cols,@i+1,len(@cols))
set @i = charindex(',',@cols)
end
if @cols <> '\'
set @sql = @sql + col_name(object_id(@tableName),convert(int,@cols))set @sql = @sql + ' from ' + @tableName exec(@sql)end