SQL SERVER – Different Methods to Know Parameters of Stored Procedure

Suppose you have a stored procedure with some input parameters. There are two methods to know the list of parameters defined in a stored procedure. Let us look at both ways to know parameters of stored procedure.

Let us create the following stored procedure

CREATE PROCEDURE TEST_PROCEDURE
(
@CUST_ID INT,
@YEAR INT
)
AS
SELECT @CUST_ID,@YEAR


Now to know the parameters used in the TEST_PROCEDURE stored procedure, you can use the following methods

1 Use SP_HELP system stored procedure

EXEC sp_HELP 'TEST_PROCEDURE'

When you execute the above method, this is the result of the second result set.

Parameter_name Type Length Prec Scale Param_order Collation
------------------ ----------- ----------- ----------- ------------------------------
@cust_id int 4 10 0 1 NULL
@year int 4 10 0 2 NULL

2 Use INFORMATION_SCHEMA.PARAMETERS system view

SELECT
PARAMETER_NAME,DATA_TYPE ,ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.PARAMETERS
WHERE
SPECIFIC_NAME='TEST_PROCEDURE'

The result is

PARAMETER_NAME DATA_TYPE ORDINAL_POSITION
------------------------ ------------ ----------------
@cust_id int 1
@year int 2

If you know any other methods feel free to post as a comment.

Tips When You Read the Parameters of Stored Procedure Code

There is one more place to look: the sys.parameters catalog view. Join it to sys.types and you get the name, data type, length, order and OUTPUT flag in one result set that is easy to filter. It is my choice when I want to check many procedures at once, for example before a release:

SELECT p.name, t.name AS type_name, p.max_length, p.is_output FROM sys.parameters p JOIN sys.types t ON p.user_type_id = t.user_type_id WHERE p.object_id = OBJECT_ID('dbo.TEST_PROCEDURE') ORDER BY p.parameter_id;

A few details surprise people:

• The length is shown in bytes, so an NVARCHAR(50) parameter shows 100, and -1 means MAX.
• The names include the @ sign, so filter with it, for example WHERE p.name = '@CustomerID'.
• The has_default_value column is only filled for CLR procedures. For normal T-SQL procedures it is always 0, even when a default exists, so read the procedure text if you need the default values.

Checking the parameter list before you change a procedure is a good habit. A new parameter without a default breaks every caller that does not pass it, so give new parameters a default value whenever you can.

The list is also useful when you call a procedure from your own code. Naming each parameter in the call, like EXEC dbo.MyProc @CustomerID = 1, keeps the call working even if someone later changes the order of the parameters, and it makes the call much easier to read.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.