-當表字段太多,假設有n個,隻查詢前面10個的函數
---create by talantlee
---4/19/05
---測試:---假如表a不超過10條記錄就返回所有記錄,超過就返回10條記錄
--declare @a varchar(100)
---set @a=hh_li.dbo.select_ncolumn('student',3)
--- exec(@a)CREATE function select_ncolumn(@tname varchar(100),@num int)
returns varchar(800)
as
begin
declare @string varchar(500)
declare @i int
set @i =1
set @string =''
while (@i<=@num)
begin
select @string=@string+','+name from syscolumns where id=object_id(@tname) and colorder= @i
set @i=@i+1
end
set @string=stuff(@string,1,1,'')return('select '+ @string+ ' from ' +@tname)
end
---create by talantlee
---4/19/05
---測試:---假如表a不超過10條記錄就返回所有記錄,超過就返回10條記錄
--declare @a varchar(100)
---set @a=hh_li.dbo.select_ncolumn('student',3)
--- exec(@a)CREATE function select_ncolumn(@tname varchar(100),@num int)
returns varchar(800)
as
begin
declare @string varchar(500)
declare @i int
set @i =1
set @string =''
while (@i<=@num)
begin
select @string=@string+','+name from syscolumns where id=object_id(@tname) and colorder= @i
set @i=@i+1
end
set @string=stuff(@string,1,1,'')return('select '+ @string+ ' from ' +@tname)
end
---create by talantlee
---4/19/05
---測試:select dbo.select_ncolumn('a',10) from a---假如表a不超過10條記錄就返回所有記錄,超過就返回10條記錄
--測試方法
--declare @a varchar(800)
--set @a=dbo.select_ncolumn('student',5)--表名,列數
--exec('select '+@a +' from student')---注意空格CREATE function select_ncolumn(@tname varchar(100),@num int)
returns varchar(800)
as
begin
declare @string varchar(500)
declare @i int
set @i =1
set @string =''
while (@i<=@num)
begin
select @string=@string+','+name from syscolumns where id=object_id(@tname) and colorder= @i
set @i=@i+1
end
set @string=stuff(@string,1,1,'')return @string
end