List parameters of Stored procedures

Posted by VIJI Saturday, July 4, 2009

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

0 Responses to List parameters of Stored procedures

Post a Comment