List parameters of Stored procedures

The following SQL Stored Procedure is used to List out the parameter names of Stored procedure.

WHERE CAN WE USE THIS PROCEDURE:

Suppose We may forget the parameter names or data types of procedures. In such situation, this procedure will be very useful.

You just need to give the procedure name as a parameter, it will display all the parameters of stored procedures.




CREATE PROCEDURE dbo.ListSPParams
@procedure_name SYSNAME
AS

BEGIN

SELECT
[Parameter Name] = COALESCE(PARAMETER_NAME, ''),

[Parameter DataType] = COALESCE(UPPER(DATA_TYPE) + CASE
WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN
'(' + CAST(NUMERIC_PRECISION AS VARCHAR)
+ ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
WHEN RIGHT(DATA_TYPE, 4) = 'CHAR' THEN
'(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
ELSE '' END + CASE PARAMETER_MODE
WHEN 'INOUT' THEN ' OUTPUT' ELSE ' ' END, '-')
FROM
INFORMATION_SCHEMA.PARAMETERS
WHERE
SPECIFIC_NAME = @procedure_name

ORDER BY

ORDINAL_POSITION
END

GO



USAGE
EXEC dbo.ListSPParams 'Stored procedure Name'


Example

exec dbo.ListSPParams 'dt_checkoutobject_u'

OUTPUT

Parameter Name Parameter DataType

@chObjectType CHAR(4)
@vchObjectName NVARCHAR(255)
@vchComment NVARCHAR(255)
@vchLoginName NVARCHAR(255)
@vchPassword NVARCHAR(255)
@iVCSFlags INT
@iActionFlag INT