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?

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