存储过程中使用sp_executesql传递自定义表类型输出参数报错求助

Ah, I see the problem here! The core issue is twofold:

1. SQL Server doesn't support using user-defined table types as OUTPUT parameters for sp_executesql
2. The @OutPutTable variable you declared in your stored procedure exists in a different scope than the dynamic SQL run by sp_executesql—so the dynamic code can't see or modify the outer variable directly.

Let me walk you through two solid solutions to get your procedure working correctly.

Solution 1: Use a Session-Scoped Temp Table #

Temp tables are visible across all scopes within your current database session, making them perfect for sharing data between your main procedure and dynamic SQL. Here's the adjusted code:

CREATE PROCEDURE attributevalues.sp_EvalClearingNetSpend 
AS 
BEGIN 
    -- Create a temp table with the same schema as your custom table type
    CREATE TABLE #TempOutput (
        StartDate DATETIME,
        EndDate DATETIME,
        Amount MONEY,
        AccountId INT
    );

    DECLARE @Sql AS NVARCHAR(MAX); 
    -- Dynamic SQL inserts directly into the temp table (which it can access)
    SET @Sql = 'INSERT INTO #TempOutput SELECT StartDate,EndDate,Amount, AccountId FROM table1'; 

    EXEC sp_executesql @Sql; 

    -- Return the results from the temp table
    SELECT * FROM #TempOutput;

    -- Optional cleanup: Temp tables are automatically dropped when your session ends
    DROP TABLE #TempOutput;
END

Notes for this approach: #

• Temp tables (#) are isolated to your session, so you won't have conflicts with other users running the same procedure.
• Make sure the temp table's schema exactly matches the columns returned by your dynamic SELECT statement.

Solution 2: Capture Results with INSERT...EXEC (Use Your Custom Table Type) #

If you want to keep using your mytabletype instead of a temp table, you can use INSERT...EXEC to directly populate your table variable from the dynamic SQL's result set. This works because INSERT...EXEC can capture results from dynamic SQL into a table variable (as long as the schemas match):

CREATE PROCEDURE attributevalues.sp_EvalClearingNetSpend 
AS 
BEGIN 
    DECLARE @OutPutTable AS mytabletype; 
    DECLARE @Sql AS NVARCHAR(MAX); 

    -- Adjust dynamic SQL to just return the result set (no INSERT into a variable)
    SET @Sql = 'SELECT StartDate,EndDate,Amount, AccountId FROM table1'; 

    -- Capture the dynamic results into your table variable
    INSERT INTO @OutPutTable
    EXEC sp_executesql @Sql; 

    -- Return the final results
    SELECT * FROM @OutPutTable;
END

Notes for this approach: #

• This preserves your use of mytabletype, which is great if you reuse this schema elsewhere in your codebase.
• No cleanup is needed—table variables are automatically discarded when the procedure finishes running.

Which Solution Should You Pick? #

• Go with Solution 2 if you want to maintain consistency with your custom table type and avoid temp table management.
• Go with Solution 1 if you need a quick, straightforward fix without relying on the custom type.

内容的提问来源于stack exchange，提问作者Emil