忙忙停停花了兩天時(shí)間弄出來(lái)的，這這里與大家共享，這個(gè)存儲(chǔ)過(guò)程實(shí)現(xiàn)后，大伙就不用重復(fù)勞動(dòng)啊！
表1
bill
0001
0002
0003
....
要根據(jù)表1得出結(jié)算如0001,0002,0003,......
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何將exec執(zhí)行結(jié)果放入變量中？
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 查詢(xún)條件字段 @orderbill 排序字段 @count_bill 要統(tǒng)計(jì)的單號(hào)，也是要產(chǎn)生字符串的字段,@bill_value 單據(jù)號(hào),@print_bill 得出的結(jié)果寫(xiě)入哪個(gè)字段
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 '結(jié)果為：'+@str_bill
end
網(wǎng)上轉(zhuǎn)載一例：
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