T-SQL存储过程 - 检测参数是否作为OUTPUT提供

Consider the following T-SQL code snippet:

请考虑以下T-SQL代码段：

CREATE PROC dbo.SquareNum(@i INT OUTPUT)
AS
BEGIN
    SET @i = @i * @i
    --SELECT @i
END
GO

DECLARE @a INT = 3, @b INT = 5
EXEC dbo.SquareNum @a OUTPUT
EXEC dbo.SquareNum @b
SELECT @a AS ASQUARE, @b AS BSQUARE
GO
DROP PROC dbo.SquareNum

The result set is:

结果集是：

ASQUARE     BSQUARE
----------- -----------
9           5

As can be seen, @b is not squared, b/c it was not passed-in as output parameter (no OUTPUT qualifier when passing in the parameter).

可以看出，@ b不是平方，b / c它没有作为输出参数传入（传入参数时没有OUTPUT限定符）。

I would like to know if there is a way I could check within stored procedure body (dbo.SquareNum body in this case) to see if a parameter has indeed been passed in as an OUTPUT parameter?

我想知道是否有一种方法可以检查存储过程体（在本例中为dbo.SquareNum体），以查看参数是否确实已作为OUTPUT参数传入？

6 个解决方案

#1

   ------ THIS WILL GIVE YOU THE BOTH VALUE IN squared------

    CREATE PROC dbo.SquareNum(@i INT OUTPUT)
    AS
    BEGIN
        SET @i = @i * @i
        --SELECT @i
    END
    GO

    DECLARE @a INT = 3, @b INT = 5
    EXEC dbo.SquareNum @a OUTPUT
    EXEC dbo.SquareNum @b OUTPUT
    SELECT @a AS ASQUARE, @b AS BSQUARE
    GO
    DROP PROC dbo.SquareNum


  -----TO CHECK STORED PROCEDURE BODY-----

    SELECT OBJECT_NAME(object_id), 
           OBJECT_DEFINITION(object_id)
    FROM  sys.procedures
    WHERE OBJECT_DEFINITION(object_id) =(SP_NAME)

#2

You can do this by query to sys views:

您可以通过查询sys视图来执行此操作：

select 
    p.name as proc_name,
    par.name as parameter_name,
    par.is_output
from sys.procedures p
inner join sys.parameters par on par.object_id=p.object_id
where p.name = 'SquareNum'

or check in Management Studio in database tree: [database] -> Programmability -> Stored Procedures -> [procedure] -> Parameters

或者在数据库树中检入Management Studio：[数据库] - >可编程性 - >存储过程 - > [procedure] - >参数

#3

Maybe I'm wrong but I don't believe it's possible. OUTPUT is part of the stored procedure definition so you should know when a parameter is or not OUTPUT. There is no way to set it dynamically so I think it's pointless to determine by code when a parameter is output or not because you already know it.

也许我错了，但我不相信这是可能的。 OUTPUT是存储过程定义的一部分，因此您应该知道参数何时是OUTPUT。没有办法动态设置它所以我认为通过代码确定何时输出参数是没有意义的，因为你已经知道它。

If you are trying to write a dynamic code, Piotr Lasota's answer should drive you to the correct way to realize when a parameter is Output.

如果您正在尝试编写动态代码，Piotr Lasota的答案应该引导您以正确的方式实现参数何时输出。

#4

Use the following query to get the name of all the parameters and to check if it is a output parameter :::

使用以下查询获取所有参数的名称并检查它是否是输出参数:::

select name, is_output from sys.parameters

#5

Actually, there is a very simple way!

实际上，有一种非常简单的方法！

Make the parameter optional by setting a default value (@Qty AS Money = 0 Below)

通过设置默认值使参数可选（@Qty AS Money = 0 Below）

Then, pass a value OTHER THAN THE DEFAULT when calling the procedure. Then immediately test the value and if it is other than the default value you know the variable has been passed.

然后，在调用过程时传递一个值，而不是DEFAULT。然后立即测试该值，如果它不是默认值，您知道该变量已被传递。

Create Procedure MyProcedure(@PN AS NVarchar(50), @Rev AS NVarchar(5), @Qty AS Money = 0 OUTPUT) AS BEGIN DECLARE @QtyPassed AS Bit = 0 IF @Qty <> 0 SET @QtyPassed = 1

创建过程MyProcedure（@PN AS NVarchar（50），@ Rev AS NVarchar（5），@ Qty AS Money = 0 OUTPUT）AS BEGIN DECLARE @QtyPassed AS Bit = 0 IF @Qty <> 0 SET @QtyPassed = 1

Of course that means the variable cannot be used for anything other than OUTPUT unless you have a default value that you know will never be used as an INPUT value.

当然，这意味着该变量不能用于除OUTPUT之外的任何其他内容，除非您有一个默认值，您知道它永远不会用作INPUT值。

#6

to view stored procedure body

查看存储过程体

exec sp_helptext ''

exec sp_helptext''