SQL Server using table valued input parameter in dynamic sql in a stored procedure

Question

I have a stored procedure that has a table valued input parameter which is derived from a user defined table type.

And I need to select some data from a table to a table variable using that input table valued parameter in dynamic SQL. Here the problem is without dynamic SQL it works fine and when I use dynamic SQL can't compile the stored procedure. And it throws this error:

Msg 137, Level 16, State 1, Procedure sp_CreateScenario_q2, Line 63
Must declare the scalar variable "@paramTable".

And I have to use dynamic SQL since my tables are dynamically generated.

Here is my code

ALTER  PROCEDURE [dbo].[sp_CreateScenario_q2]
(
    @bookId                                           INT,
    @scenarioName                                     NVARCHAR(100),
    @paramTable [planning].[HierarchyAllocationInput] READONLY,
    @userId                                           INT              
) 
AS 
BEGIN

DECLARE @DemandStructureId  INT,
@AreaCode           NVARCHAR(20),
@Query              NVARCHAR(MAX),
@BucketId                   INT;

DECLARE @DemandData TABLE(
FGDemandId nVARCHAR(100),
DemandQty DECIMAL(18,2),
ProductNo NVARCHAR(100),
LocationNo NVARCHAR(100),
Season NVARCHAR(100),
Year INT

);


SELECT  @DemandStructureId  = PlanningStructureId,@BucketId = BucketId
    FROM    [planning].[Book]
    WHERE   BookId = @bookId

    SELECT  @AreaCode           = PlanningAreaCode 
    FROM    [config].[PlanningStructure] LEFT JOIN 
            [config].[PlanningArea] ON  PlanningStructure.PlanningAreaId = PlanningArea.PlanningAreaId
    WHERE   PlanningStructureId = @DemandStructureId

    IF @BucketId = 1 
        BEGIN

        SET @Query = 'select d.FGDemandId,d.DemandQty,d.ProductNo,d.LocationNo,d.Season,d.Year 
                      from '+ @paramTable +'
                            left join [dbo].[SN_STAGING_ProductReference] pr ON 
                                    pr.ProductReferenceId = t1.ProductReferenceId
                            left join '+ quotename(@AreaCode) +'.[SN_STAGING_FGDemand] d ON
                                    d.HierarchyId = t1.PlanningHierarchyId 
                                AND ISNULL(d.ProductNo,'') = ISNULL(pr.ProductNo,'')
                                AND ISNULL(d.LocationNo,'')  = ISNULL(pr.LocationNo,'') 
                                AND d.Year = t1.PlanningYear
                                AND ISNULL(d.Season,'') = ISNULL(t1.PlanningSeason,'')
                                AND d.IsActive = 1'

            INSERT INTO @DemandData EXECUTE(@Query);



        END
SELECT * FROM @DemandData;
END

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
The @paramTable is not known inside the dynamic query... If there aren't to many data inside, you could dynamically create a CTE simulating your type variable... Another choice could be a temp table. — Shnugo

Circle Hsiao Circle Hsiao · Accepted Answer · 2020-05-25T06:14:36

I have the same use case and I switch from EXEC(query) to sp_executesql which provides the functionality to layout the parameters inside the dynamic query.

Take your code as an instance,

SET @Query = 'select d.FGDemandId,d.DemandQty,d.ProductNo,d.LocationNo,d.Season,d.Year 
              from @dynamicParamTable 
              left join [dbo].[SN_STAGING_ProductReference] pr ON pr.ProductReferenceId = t1.ProductReferenceId 
              left join '+ quotename(@AreaCode) +'.[SN_STAGING_FGDemand] d ON d.HierarchyId = t1.PlanningHierarchyId 
                AND ISNULL(d.ProductNo,'') = ISNULL(pr.ProductNo,'')
                AND ISNULL(d.LocationNo,'')  = ISNULL(pr.LocationNo,'') 
                AND d.Year = t1.PlanningYear
                AND ISNULL(d.Season,'') = ISNULL(t1.PlanningSeason,'')
                AND d.IsActive = 1'

INSERT INTO @DemandData EXECUTE sp_executesql @Query, N'@dynamicParamTable [planning].[HierarchyAllocationInput] READONLY', @dynamicParamTable = @paramTable

SQL Server using table valued input parameter in dynamic sql in a stored procedure

1 Answers