动态表名，列名，输入参数，输出参数等

本例实现动态表名，列名，输入参数，输出参数等进行操作。

先准备一些数据：

if object_id('tempdb.dbo.#part') is not null drop table #part

create table #part (
    [id] int,
    [item] nvarchar(40),
    [category] nvarchar(25),
    [qty] decimal(18,2)
)
insert into #part ([id],[item],[category],[qty]) values (23394,'i32-gg443-qt0098-0001','s',423.65),
                                                        (45008,'i38-aa321-ws0098-0506','b',470.87),
                                                        (14350,'k38-12321-5456ud-3493','b',200.28),
                                                        (64582,'872-rtde3-q459pw-2323','t',452.44),
                                                        (23545,'098-ssss1-ws0098-5526','s',500.00),
                                                        (80075,'b78-f1h2y-5456ud-2530','t',115.06),
                                                        (53567,'po0-7g7g7-jjy098-0077','q',871.33),
                                                        (44349,'54f-art43-6545nn-2514','s',934.39),
                                                        (36574,'x3c-sdewe-3er808-8764','q',607.88),
                                                        (36574,'rvc-43ase-h43qww-9753','u',555.19)

go
select [id],[item],[category],[qty] from #part
go

 

要求是提取以[category] 为??的[qty]总数。另外本例要求是动态提取。

 

declare @table_name  sysname = '#part', 
@groupby_column sysname = '[category]',
@sum_column sysname = '[qty]',
@param_value nvarchar(2) = n's',
@output_result decimal(18,2) 

declare  @sql nvarchar(max) = n'
    select @output_result = sum('+ @sum_column +') 
    from '+ @table_name +'  
    where '+ @groupby_column +' = @param_value 
    group by '+ @groupby_column +''
execute sp_executesql @sql,
                      n'@param_value nvarchar(2),@output_result decimal(18,2) output',
                      @param_value,@output_result output


select @output_result