如何在SQL Server存储过程/函数中声明输入输出参数?

问题描述

在Oracle中，

我们可以声明输入输出参数(不仅仅是输入或输出)，如下所示:

We can declare an input-output parameters (not just input or output) like the following:

Create Or Replace Procedure USERTEST.SimpleInOutProcedure(
    p_InputInt Int,
    p_OutputInt out Int,
    p_InputOutputInt in out Int
) AS
BEGIN
    p_OutputInt := p_InputInt + 1;
    p_InputOutputInt := p_InputOutputInt + p_InputOutputInt;
END;

但是，当我尝试在SQL Server中声明时，脚本无法编译:

However, as I try to declare such in SQL Server, the script would not compile:

create procedure SimpleInOutProcedure
    @p_InputInt int, @p_OutputInt int input output
As
Begin
    Select * from TestTableCommonA;
End

输入"一词不是必需的，因此在SQL Server Management Studio中不是蓝色:

The word "input" is not expected, thus not blue-colored in the SQL Server Management Studio:

我的脚本怎么了，如何在SQL Server存储过程/函数中声明输入输出参数?

What's wrong with my script, how to declare input-output parameters in SQL Server Stored Procedure/Function?

我要这样做是因为我需要为SQL Server DB创建等效"读取器，该读取器以前是为Oracle DB创建的，并且具有用于输入/输出参数的读取器.

I want to do this because I need to create "equivalent" reader for SQL Server DB which previously was created for Oracle DB and has the reader for in/out parameters.

推荐答案

如果将参数声明为OUTPUT，则它既充当Input又充当OUTPUT

If you declare a parameter as OUTPUT, it acts as Both Input and OUTPUT

CREATE PROCEDURE SimpleInOutProcedure
(
    @p_InputInt  INT,
    @p_OutputInt INT OUTPUT
)
AS
BEGIN
    SELECT
       @p_OutputInt = @p_OutputInt
END
GO

DECLARE @p_OutputInt int = 4
EXEC SimpleInOutProcedure @p_InputInt = 1, @p_OutputInt = @p_OutputInt OUTPUT
SELECT @p_OutputInt

这篇关于如何在SQL Server存储过程/函数中声明输入输出参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！