主页 / dba / 问题 / 153738
Accepted
Edgar Allan Bayron
Asked: 2016-10-31 14:26:08 +0800 CST

带输出参数的 SQL 存储过程

  • 772

我知道如何编写带有输出参数的存储过程。但我不知道为什么我会使用它而不是仅仅使用一个简单的SELECT语句。一个普通的存储过程仍然可以在一个网格中返回一个输出（见下面的例子）。

任何人都可以举一个真实的例子，我可以使用带输出参数的 SP 和不带输出参数的 SP 吗？

例子

-- Using output parameter
SELECT @var = COUNT(*) FROM table1 WHERE gender = @gender...

-- Without output parameter
SELECT COUNT(*) FROM table WHERE gender = @gender...
sql-server t-sql

3 个回答

  1. Best Answer

    存储过程中的输出参数对于将值传递回调用 T-SQL 很有用，然后调用 T-SQL 可以将该值用于其他事情。

    假设您有一个存储过程，它返回给定城市输入的状态，状态作为输出参数：

    CREATE PROCEDURE [dbo].[GetStateFromCity] (@City NVARCHAR(30), @State NCHAR(2) OUTPUT)
AS
SELECT @State = [State]
FROM [ExampleTable] 
WHERE [City] = @City
GO;

    您现在可以使用此输出参数在别处传递一个值。

    例如：

    DECLARE @State int
EXEC [dbo].[GetStateFromCity] @City = 'Calgary', @State OUTPUT;

-- Do something with this value
SELECT @State;

-- Do something else
EXEC [dbo].[GetInsuranceCompanyByState] @State;

    总而言之，如果您只想为客户端应用程序返回一个值，您可能不需要输出参数。

    但是，如果您想在 T-SQL 中的存储过程之间传递值，它们会非常有用。

    对于它的价值，我几乎不使用输出参数。

    • 4

  2. 假设这个问题与 SQL Server 有关：它归结为上下文和效率。

    上下文 = 应用程序代码

    从应用程序代码执行存储过程时，在代码量方面并没有太大区别。返回结果集时，只需调用ExecuteReaderthenSqlDataReader.Read()获取一行，然后从SqlDataReader. ExecuteScalar但是，如果您只是获取单个值，那么您可以使用获取一行（即使有更多行）并返回第一列中的值（即使有更多列）的快捷方法。返回OUTPUT参数时，只需要调用ExecuteNonQuery，检查.Value每个参数的属性，并转换为合适的类型。

    因此，就返回单个值的简单示例而言，返回结果集并调用ExecuteScalar. 但是，返回结果集，无论是使用ExecuteReader还是ExecuteScalar，都需要 SQL Server 和客户端应用程序的更多资源。SQL Server 需要设置和管理结果集（即需要内存和时间），应用程序需要实例化 a SqlDataReader（是的，即使使用ExecuteScalar）并管理它（即需要内存和时间）。如果保证您只有一个结果集行，那么最好使用输出参数（即使只有一点点）。

    上下文 = T-SQL

    OUTPUT当从 T-SQL 执行存储过程（并且需要使用返回值）时，使用参数至少更方便。返回结果集是可用的，但需要使用INSERT ... EXEC. 但是您仍然需要将行选择为局部变量。同样，需要更多的时间和资源来获得变量中的值。

    现在，当您只返回一个值（即适用于 的相同情况）时，有时ExecuteScalar您可以使用标量用户定义函数 (UDF)，它能够放入查询中，这有时非常有用（即使在查询中使用标量 UDF 会降低性能）。但是，在 UDF 中可以执行的操作有很多限制，因此如果您需要创建临时表，或者执行任何 DML 或 DDL 等，那么使用存储过程是唯一的选择。

    虽然不是“结果集与输出参数”问题的一部分，但请记住，您可以同时做这两个问题！如果您有一些谨慎的值以及要返回的一组数据，存储过程确实允许返回两者，这在极少数情况下非常有用。

    • 4

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

    1) 您可以传递多个参数为OUTPUT,

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

    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

    调用此例程：

    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

    返回 NULL、NULL、NULL

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

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

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

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

    （和上次调用一样，因为我们没有通过 using 获取新值OUTPUT，所以它保留了旧值。） 

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

    返回 16, "A 的值为 5，B 的值为 16", 1

    • 3

相关问题