动态表名,列名,输入参数,输出参数等
本例实现动态表名,列名,输入参数,输出参数等进行操作。
先准备一些数据:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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]总数。另外本例要求是动态提取。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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