SQL Server 输出参数问题

Question

提问by George2

I am using SQL Server 2008 Enterprise. I am learning OUTPUT parameter of SQL Server stored procedure. For example, stored procedure sp_add_jobschedule has an OUTPUT parameter called schedule_id.

我正在使用 SQL Server 2008 企业版。我正在学习 SQL Server 存储过程的 OUTPUT 参数。例如，存储过程 sp_add_jobschedule 有一个名为 schedule_id 的 OUTPUT 参数。

http://msdn.microsoft.com/en-us/library/ms366342.aspx

My confusion is, looks like OUTPUT parameter could be provided an input value and also returns a value, looks like it has behaviors of both INPUT and OUTPUT parameter? Is it allowed not to provide any INPUT values for OUTPUT parameter (to make it look like pure output parameter behavior)?

我的困惑是，看起来 OUTPUT 参数可以提供输入值并返回一个值，看起来它具有 INPUT 和 OUTPUT 参数的行为？是否允许不为 OUTPUT 参数提供任何 INPUT 值（使其看起来像纯输出参数行为）？

Answer 1

回答by marc_s

The confusion is justified to a degree - and other RDBMS like Oracle do have stored procedure parameters which can be of type IN(input only), OUT(output only), or INOUT(both ways - "pass by reference" type of parameter).

这种混淆在一定程度上是有道理的——像 Oracle 这样的其他 RDBMS 确实有存储过程参数，这些参数可以是类型IN（仅输入）、OUT（仅输出）或INOUT（两种方式——“通过引用传递”类型的参数）。

SQL Server is a bit sloppy here since it labels the parameter as OUTPUT, but really, this means INPUT/OUTPUT- it basically just means that the stored proc has a chance of returning a value from its call in that parameter.

SQL Server 在这里有点草率，因为它将参数标记为OUTPUT，但实际上，这意味着INPUT/ OUTPUT- 它基本上只是意味着存储过程有机会从该参数中的调用中返回一个值。

So yes - even though it's called OUTPUTparameter, it's really more of an INPUT/OUTPUTparameter, and those IN, INOUT, OUTlike in Oracle do not exist in SQL Server (in T-SQL).

所以，是的-尽管它被称为OUTPUT参数，它是真正的性质转向INPUT/OUTPUT参数，而这些IN，INOUT，OUT像甲骨文不会在SQL Server中存在（在T-SQL）。

Answer 2

回答by Sergey Olontsev

I can give you short example on how to create stored procedure with output parameter.

我可以为您提供有关如何使用输出参数创建存储过程的简短示例。

CREATE PROCEDURE test_proc

@intInput int,
@intOutput int OUTPUT

AS
set @intOutput = @intInput + 1 

go

And to call this procedure and then use output parameter do as following:

并调用此过程然后使用输出参数执行以下操作：

declare @intResult int
exec test_proc 3 ,@intResult OUT
select @intResult

You see, that you should declare ouput variable first. And after executing stored procedure, the output value will be in your variable. You can set any value to your output variable, but after executing stored procedure it will contain exactly what stored procedure return (no matter what value was in the output variable).

你看，你应该先声明输出变量。执行存储过程后，输出值将在您的变量中。您可以为输出变量设置任何值，但在执行存储过程后，它将准确包含存储过程返回的内容（无论输出变量中的值是什么）。

For example:

例如：

declare @intResult int
exec test_proc 3 ,@intResult OUT
select @intResult

It will return 4. And:

它将返回 4。并且：

declare @intResult int
set @intResult = 8
exec test_proc 3 ,@intResult OUT
select @intResult

Also return 4.

也返回 4。

Answer 3

回答by Matt

Yes, you can use an OUTPUT parameter for both passing in and retrieving values (although I can't think of a good reason to do that at the moment).

是的，您可以使用 OUTPUT 参数来传入和检索值（尽管我目前想不出这样做的充分理由）。

Here's a trivial example that demonstrates this:

这是一个简单的例子来证明这一点：

-- The stored procedure
CREATE PROCEDURE OutParamExample
    @pNum int OUTPUT
AS
BEGIN
    select @pNum
    set @pNum = @pNum + 5
END
GO

-- use a local variable to retrieve your output param value
declare @TheNumber int
set @TheNumber = 10

print @TheNumber
exec OutParamExample @TheNumber OUTPUT
print @TheNumber

The results will look like this:

结果将如下所示：

10

-----------
10

(1 row(s) affected)

15

EDIT:OK, I think I missed a "not" in the second paragraph and may not have answered the question you asked. If you want a strict output parameter (e.g. something like a return code), you certainly don't have to provide a valueto the local variable passed as the output parameter, but you still have to declare that local variable so that you'll have a way of accessing the returned value outside of the scope of the procedure itself.

编辑：好的，我想我错过了第二段中的“不”，可能没有回答您提出的问题。如果您想要一个严格的输出参数（例如返回码之类的东西），您当然不必为作为输出参数传递的局部变量提供值，但您仍然必须声明该局部变量，以便您有一种方法可以访问过程本身范围之外的返回值。

For example:

例如：

declare @LocalNumber int
-- I don't have to assign a value to @LocalNumber to pass it as a parameter
exex OutParamExample @LocalNumber OUTPUT  
-- (assume SP has been altered to assign some reasonable value)

-- but I do have to declare it as a local variable so I can get to
-- the return value after the stored procedure has been called
print @LocalNumber

Answer 4

回答by shahkalpesh

Adding further to what others have said above, OUTPUT parameters can act as INPUT as well as OUTPUT. But, it depends on the stored procedure to use the value passed to it.

进一步补充上面其他人所说的，OUTPUT 参数可以充当 INPUT 和 OUTPUT。但是，使用传递给它的值取决于存储过程。

If the stored procedures ignores the value passed in for OUTPUT parameter (which it generally should), the INPUT value gets ignored anyway.

如果存储过程忽略为 OUTPUT 参数传入的值（通常应该这样做），则无论如何都会忽略 INPUT 值。

Using Sergey's code, I can use the value user passed for @intOutput (if I need to)

使用 Sergey 的代码，我可以使用用户为 @intOutput 传递的值（如果需要）

create PROCEDURE test_proc

@intInput int,
@intOutput int OUTPUT

AS
set @intOutput = @intOutput + 1 

go

But, that defeats the purpose of OUTPUT parameter.
To give a different view, c# compiler forces you to overwrite the out parameter's value by assignment (without using the output parameter).

但是，这违背了 OUTPUT 参数的目的。
为了给出不同的视图，c# 编译器强制您通过赋值覆盖输出参数的值（不使用输出参数）。

e.g. I cannot do this in c#. Here it acts as only out parameter (i.e ignore the value user passed to this function)

例如，我不能在 c# 中做到这一点。这里它只作为输出参数（即忽略用户传递给这个函数的值）

static void dosomething(out int i)
{
    //i = 0;
    i = i + 1;
}

Answer 5

回答by blueling

One additional note regarding part of the original question:

关于原始问题的一部分的附加说明：

"Is it allowed not to provide any INPUT values for OUTPUT parameter (..)?"

“是否允许不为 OUTPUT 参数 (..) 提供任何 INPUT 值？”

In SQLServer it is possible to specify a default value for an OUTPUT parameter (which as others have pointed out actually INOUT). Consider the following example in which you could specify a explicit value or let the function itself generate an ID.

在 SQLServer 中，可以为 OUTPUT 参数指定默认值（正如其他人指出的实际上是 INOUT）。考虑以下示例，您可以在其中指定显式值或让函数本身生成 ID。

CREATE PROCEDURE test (@id uniqueidentifier = NULL OUTPUT) AS
BEGIN
  IF @id IS NULL SET @id = NEWID()
  -- INSERT INTO xyz (...) VALUES (@id, ...)
  PRINT 'Insert with id: ' + CAST (@id as nvarchar(36))
END
GO

DECLARE @insertedId uniqueidentifier
EXECUTE test '00000000-0000-0000-0000-000000000000'
EXECUTE test @insertedId OUTPUT
PRINT @insertedId

DROP PROCEDURE test

This prints

这打印

Insert with id: 00000000-0000-0000-0000-000000000000
Insert with id: 67AE3D27-8EAB-4301-B384-30EEA1488440
67AE3D27-8EAB-4301-B384-30EEA1488440

Answer 6

回答by Tomalak

The question is - whydo you want to disallow to provide input? This makes no sense whatsoever. Consider this simple example:

问题是 -你为什么要禁止提供输入？这没有任何意义。考虑这个简单的例子：

CREATE PROCEDURE test (@param AS INT OUTPUT) AS
BEGIN
  SET @param = 100
END
GO

DECLARE @i INT
SET @i = 0

EXECUTE test @i OUTPUT
PRINT @i

DROP PROCEDURE test

This prints

这打印

See - how are you supposed to get a value outif you do not put a variable infirst?

见-怎么是你应该得到的值了，如果你不把一个变量在第一？

Answer 7

回答by Svetlozar Angelov

Think about OUTPUT PARAMETERS as passed by reference in programming languages, it's the same. The best example I can think right now is returning error code. You want some insert... if you select the return code from the SP you have to fetch it back in your code, with OUTPUT parameter you don't have, it will be already in your parameter (I mean using C# commands, PHP init stored proc methods, or something different then constructing strings)

想想在编程语言中通过引用传递的输出参数，它是一样的。我现在能想到的最好的例子是返回错误代码。你想要一些插入...如果你从 SP 中选择返回码，你必须在你的代码中取回它，你没有 OUTPUT 参数，它已经在你的参数中（我的意思是使用 C# 命令，PHP init 存储过程方法，或与构造字符串不同的东西）

Answer 8

回答by Dan

Two other things I think that are worth noting:

我认为还有两件事值得注意：

1) You can pass more than one parameter as OUTPUT,

1）您可以传递多个参数OUTPUT，

2) You do not have to call the parameters with OUTPUTif you don't want the results

2）OUTPUT如果您不想要结果，则不必调用参数

CREATE PROCEDURE ManyOutputs @a int, @b int output, @c varchar(100) output, @d bit output
AS
BEGIN
    SET @b = @a + 11
    SET @c = 'The Value of A is ' + CAST(@a AS varchar(5)) + ', and the value of B is ' + CAST(@b AS varchar(5))
    IF (@a % 2 = 1)
        SET @d = 1
    ELSE
        SET @d = 0
END
GO

Calling this routine:

调用这个例程：

DECLARE @bVal int
DECLARE @cVal varchar(100)
DECLARE @dVal bit

EXEC ManyOutputs 1, @bVal, @cVal, @dVal
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Returns NULL, NULL, NULL

返回 NULL、NULL、NULL

EXEC ManyOutputs 2, @bVal OUT, @cVal OUT, @dVal OUT
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Returns 13, "The Value of A is 2, and the value of B is 13", 0

返回 13, "A 的值为 2，B 的值为 13", 0

EXEC ManyOutputs 3, @bVal, @cVal, @dVal
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Returns 13, "The Value of A is 2, and the value of B is 13", 0

返回 13, "A 的值为 2，B 的值为 13", 0

(the same as the last call, because we didn't get new values by using OUTPUT, so it retained the old values.)

（与上次调用相同，因为我们没有通过 using 获取新值OUTPUT，因此保留了旧值。）

EXEC ManyOutputs 5, @bVal OUT, @cVal OUT, @dVal OUT
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Returns 16, "The Value of A is 5, and the value of B is 16", 1

Returns 16, "A的值为5，B的值为16", 1

SQL Server 输出参数问题

提问by George2

回答by marc_s

回答by Sergey Olontsev

回答by Matt

回答by shahkalpesh

回答by blueling

回答by Tomalak

回答by Svetlozar Angelov

回答by Dan

相关推荐

最近更新

标签

SQL Server 输出参数问题

提问by George2

回答by marc_s

回答by Sergey Olontsev

回答by Matt

回答by shahkalpesh

回答by blueling

回答by Tomalak

回答by Svetlozar Angelov

回答by Dan

相关推荐

SQL Server 忽略 where 表达式中的大小写

如何通过 Sum SQL 和 Spring Data JPA 使用组？

SQL - 两个外连接

从字符串列名 sql 中获取列值

相关推荐

最近更新

标签