T-SQL stored procedure - Detecting if a parameter is supplied as OUTPUT

Question

Consider the following T-SQL code snippet:

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).

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?

I agree with Giorgi - don't think you can check that at run time. If you are worried about enforcing it you might want to do this as a scalar function that returns the new value instead of using a procedure. — David Godwin, Apr 26 '16 at 13:36
Interesting question. I don't have an answer, but out of curiosity, why would you want to do this? I wonder if there's an underlying problem that could be solved in another way. — Joe Farrell, Jul 12 '16 at 15:39

score 1 · Answer 1 · answered Sep 01 '17 at 13:00

   ------ 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)

score 1 · Answer 2 · answered Aug 13 '18 at 07:58

1

Actually, there is a very simple way!

Make the parameter optional by setting a default value (@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.

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

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.

answered Aug 13 '18 at 07:58

KimM

17
4

This allows you to detect if the parameter has been passed at all, but it does not allow you to detect if it was passed, but not as `OUTPUT` -- and that's exactly what the question was concerned about. The only saving grace is that, in cases where the parameter is *only* used for `OUTPUT` (i.e. it's not an in-out parameter) callers are likely to never fill in the value, allowing you to test for `NULL`. The question unfortunately concerns an in-out parameter (although that might just have been for illustration). – Jeroen Mostert Aug 13 '18 at 09:35

score 0 · Answer 3 · answered Apr 18 '16 at 10:50

0

You can do this by query to sys views:

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

answered Apr 18 '16 at 10:50

Piotr Lasota

201
1
6

7

I think the question is about how do detect whether the `OUTPUT` keyword was used in the code calling the stored procedure, not about the stored procedure definition. – Ed Harper Apr 18 '16 at 10:51

score 0 · Answer 4 · answered Jul 12 '16 at 15:34

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.

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.

score 0 · Answer 5 · edited Feb 03 '20 at 21:22

0

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

edited Feb 03 '20 at 21:22

Dale K

25,246
15
42
71

answered Jun 12 '17 at 08:57

PrinceKayastha

115
6

T-SQL stored procedure - Detecting if a parameter is supplied as OUTPUT

5 Answers5