T-SQL stored procedure - detect if a parameter is provided 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 

Result set:

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

As you can see, @b not a square, b / c it was not passed as an output parameter (there is no OUTPUT when passing in the parameter).

I would like to know if there is a way to check inside the body of the stored procedure (body dbo.SquareNum in this case) to see if the parameter is really passed as an OUTPUT parameter?

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

Answer 2

You can do this on request in 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 the database tree: [database] → Programmability → Stored procedures → [procedure] → Parameters

Answer 3

Maybe I'm wrong, but I do not think it is possible. OUTPUT is part of the definition of a stored procedure, so you should know when the parameter is or not OUTPUT. It is not possible to set it dynamically, so I find it pointless to determine the code when the parameter exits or not because you already know it.

If you are trying to write dynamic code, Piotr Lasota's answer should lead you to the correct implementation path when the parameter is Output.

Answer 4

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

 select name, is_output from sys.parameters 

Answer 5

to view the body of a stored procedure

exec sp_helptext ''