在MSSQL Select中執行EXEC回傳Value
在這裡面最大的問題是,我的變數就是欄位名稱,例如 sTable中有一個欄位為Item,並且只要一筆
參數就會是 @table nvarchar(200), @item nvarchar(200)
我的SQL語句為:
Select TOP(1) Item From sTable
因此在正常的EXEC下就會這樣設定
EXEC ('Select TOP(1) Item From sTable')
但我需要的是
SELECT * FROM EXEC ('Select TOP(1) Item From sTable')
錯誤訊息為 Incorrect syntax near the keyword 'EXEC'.
這個部份不論是回傳數值還是回傳表都沒有關係
我想了兩個方式,第一個是Store Procedure,另一個是Func,
但是如果是Store Procedure話一樣需要EXEC執行,而Func是在裡面不可以執行EXEC
CREATE FUNCTION [fnGetWp] (@table nvarchar(200), @item nvarchar(200))
RETURNS nvarchar(1000)
AS
BEGIN
DECLARE @statement nvarchar(max)
SET @statement = 'SELECT TOP(1) ' + @item + ' AS Item FROM ' + @table
DECLARE @value nvarchar(1000)
exec sp_executesql @statement, N'@value NUMERIC out', @value OUT
RETURN @value
END
錯誤訊息 Only functions and some extended stored procedures can be executed from within a function.
-- Store Procedure
SELECT * FROM EXEC xp_query @table = 'sTable', @item = 'Item'
-- error message: Incorrect syntax near the keyword 'EXEC'.
-- function
SELECT [dbo].fnGetWp('sTable', 'Item')
-- error message: Only functions and some extended stored procedures can be executed from within a function.