在动态查询中分配存储过程的输出参数

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

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