如何在 SQL Server 存储过程/函数中声明输入输出参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49129536/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How To Declare Input-Output Parameters In SQL Server Stored Procedure/Function?
提问by Ian
In Oracle,
在甲骨文中，
We can declare an input-output parameters (not just input oroutput) 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;
However, as I try to declare such in SQL Server, the script would not compile:
但是，当我尝试在 SQL Server 中声明时，脚本无法编译：
create procedure SimpleInOutProcedure
@p_InputInt int, @p_OutputInt int input output
As
Begin
Select * from TestTableCommonA;
End
The word "input" is not expected, thus not blue-colored in the SQL Server Management Studio:
“输入”这个词不是预期的，因此在 SQL Server Management Studio 中不是蓝色的：
What's wrong with my script, how to declare input-output parameters in SQL Server Stored Procedure/Function?
我的脚本有什么问题，如何在 SQL Server 存储过程/函数中声明输入-输出参数？
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.
我想这样做是因为我需要为 SQL Server DB 创建“等效”读取器，该读取器以前是为 Oracle DB 创建的，并且具有用于输入/输出参数的读取器。
回答by Jayasurya Satheesh
If you declare a parameter as OUTPUT, it acts as Both Input and OUTPUT
如果您将参数声明为 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
回答by ravi polara
This is sample code for SQL Input & Output parameter.
这是 SQL 输入和输出参数的示例代码。
CREATE PROCEDURE [dbo].[sample_Insert]
@name varchar(500),
@entryby int,
@RetVal INT = 0 OUT
AS
SET NOCOUNT ON
INSERT INTO dbo.Master
( name ,
entry_date ,
entry_by
)
VALUES ( @name , -- service_name - varchar(1000)
dbo.GetActDate() , -- entry_date - datetime
@entryby -- entry_by - int
)
IF @@ERROR = 0
BEGIN
SET @RetVal = 1 -- 1 IS FOR SUCCESSFULLY EXECUTED
End
ELSE
BEGIN
SET @RetVal = 0 -- 0 WHEN AN ERROR HAS OCCURED
End
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
