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

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

先准备一些数据：

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