表作为SQL中的Exec Query的表值函数的参数

I have following Query which returns Error like 'Must declare the scalar variable "@tbl"'.

我有以下Query返回错误，如'必须声明标量变量'@tbl“'。

declare   @tbl  as ItemName_Id_Table
                 ,@Entry_Date_To varchar(50) = '2017-10-22'
                 ,@qry nvarchar(max)
set @qry = 
    'SELECT        
            tblStockLedger.item_id, tblStockLedger.inward_qty, tblStockLedger.inward_qty2, Fn_StockValue_1.Value
    FROM    tblStockLedger 
        LEFT OUTER JOIN dbo.Fn_StockValue('''+@Entry_Date_To+''',@tbl) AS Fn_StockValue_1 
            ON tblStockLedger.item_id = Fn_StockValue_1.item_id
    GROUP BY 
            tblStockLedger.item_id, tblStockLedger.inward_qty, tblStockLedger.inward_qty2, Fn_StockValue_1.Value'
exec(@qry)

Could any one please explain me how to overcome this Error.

任何人都可以解释我如何克服这个错误。

1 个解决方案

#1

You need it use SP_EXECUTESQL to pass the table type to function inside dynamic query. You can also parameterize @Entry_Date_To variable instead of string concatenation

您需要使用SP_EXECUTESQL将表类型传递给动态查询中的函数。您还可以参数化@Entry_Date_To变量而不是字符串连接

DECLARE @tbl           AS ITEMNAME_ID_TABLE, 
        @Entry_Date_To date = '2017-10-22',  --changed to date
        @qry           NVARCHAR(max) 

SET @qry = 'SELECT tblStockLedger.item_id, 
                   tblStockLedger.inward_qty, 
                   tblStockLedger.inward_qty2, 
                   Fn_StockValue_1.Value             
            FROM tblStockLedger  
            LEFT OUTER JOIN dbo.Fn_StockValue(@Entry_Date_To,@tbl) AS Fn_StockValue_1                  
                         ON tblStockLedger.item_id = Fn_StockValue_1.item_id         
            GROUP BY tblStockLedger.item_id, 
                     tblStockLedger.inward_qty, 
                     tblStockLedger.inward_qty2, 
                     Fn_StockValue_1.Value'

EXEC Sp_executesql 
    @qry, 
    N'@tbl ItemName_Id_Table READONLY, @Entry_Date_To Date', 
    @tbl,@Entry_Date_To

Note : You are passing empty @tbl table variable to function

注意：您将空的@tbl表变量传递给函数

#1