忙忙停停花了兩天時間弄出來的，這這里與大家共享，這個存儲過程實現后，大伙就不用重復勞動啊！
表1
bill
0001
0002
0003
....
要根據表1得出結算如0001,0002,0003,......
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何將exec執行結果放入變量中？
declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from t_paystock_h '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
alter procedure usp_Tolineout
@table varchar(50),
@wherebill varchar(20),
@orderbill varchar(20),
@count_bill varchar(20),
@bill_value varchar(20),
@print_bill varchar(1000)
as--Author fly
begin
-- @table 表名 @wherebill 查詢條件字段 @orderbill 排序字段 @count_bill 要統計的單號，也是要產生字符串的字段,@bill_value 單據號,@print_bill 得出的結果寫入哪個字段
declare @cw_count int
declare @i int
declare @sql nvarchar(4000),
@sql2 nvarchar(4000),
@str_bill nvarchar(4000)
set @i=1
set @cw_count=1
set @sql = N'select @count = count(distinct '+@count_bill+') from '+@table+' where '+@wherebill+'='+''''+@bill_value+''''+''
exec sp_executesql @sql,N'@count int output',@cw_count output
while (@i<=@cw_count)
begin
set @sql2 = N'select top 1 @strbill=isnull(convert(varchar(100),@strbill),'''')+case isnull(@strbill,'''') when '''' then '''' else '','' end +convert(varchar(30),'+@count_bill+') from
(select top '+convert(varchar(10),@i,126)+' '+@count_bill+','+@wherebill+' from '+@table+' where bill='''+@bill_value+''' order by '+@orderbill+' desc) a
where '+@wherebill+'='''+@bill_value+''' order by '+@orderbill+' asc'
exec sp_executesql @sql2,N'@strbill varchar(500) output',@str_bill output
set @i=@i+1
end /***/
print '結果為：'+@str_bill
end
網上轉載一例：
create table tb(truename varchar(20),shuxue int,yuwen int,yinyu int)
insert tb select
'張三', 100 ,80, 80 union all select
'李四', 90 ,90 ,60 union all select
'王五', 59, 80 ,44
select truename,fz=shuxue+yuwen+yinyu ,1 as id into #t
from tb
declare @s varchar(4000)
select @s=isnull(@s+',','')+'max(case when truename='''+truename+''' then fz else 0 end) ['+truename+']'
from #t
set @s='select id,'+@s+' from #t group by id'
exec(@s)
id 張三 李四 王五
----------- ----------- ----------- -----------
1 260 240 183
(1 行受影響)
drop table tb
drop table #t