sql-server - 在动态查询中分配存储过程的 OUTPUT 参数

Question

关于这个存储过程：

CREATE PROCEDURE Test
    (@outparam int OUTPUT)
AS
BEGIN
    DECLARE @SQL VARCHAR(1000)

    SET @SQL = '
    DECLARE @outparam int
    IF (1=0)
        PRINT ''do something here. I use dynamic stuff in IF and in here ''
    ELSE IF (1=0)
        PRINT ''do something here. I use dynamic stuff in IF and in here ''
    ELSE if (1=1)
    BEGIN
        SET @outparam = 5
    END
    '

    EXEC (@SQL)
END

我需要@outparam在动态查询中分配，因为所有逻辑都在那里。

它不起作用。我明白为什么，但我不知道如何解决它

DECLARE @out int
EXEC Test @outparam = @out OUTPUT

SELECT @out

标签： sql-servertsqlsql-server-2012

Answer 1

感谢@DaleK 这行得通

CREATE procedure Test

(
    @outparam int OUTPUT
)
as

BEGIN
    DECLARE @SQL NVARCHAR(1000)

SET @SQL = '
IF (1=0)
    print ''do something here. i use dynamic stuff in IF and in here ''
ELSE IF (1=0)
    print ''do something here. i use dynamic stuff in IF and in here ''
ELSE if (1=1)
BEGIN
    SET @outparam = 5
END
'

EXEC sp_executesql @sql,
N'@outparam VARCHAR(MAX) OUTPUT',
@outparam OUTPUT;
END