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

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
Hey DBA – Watch Your Vendors! Hey Vendors – Try a Little Harder! – Notes from the Field #077
Next Post
SQL SERVER – Generate Different Random Number for Each Group Using RAND() Function

Related Posts

10 Comments. Leave new

  • Matias Luis Sincovich
    April 24, 2015 7:06 am

    Best and more practical to me is double click on SP name (or manually select the proc name) and then ALT + F1. Same for tables.

    Reply
  • You can check the parameters used in the XML execution plan under the tag.

    Reply
  • I’ve used a query similar to what was shown but have expanded on what is returned. I wrote this a few years ago to analyze the parameters for all report stored procedures.

    Select SProcID = O.object_id,
    SPName = O.name,
    SPCreateDate = O.create_date,
    ParmName = Ap.name,
    DataTypeDisplay =
    Case When t.Name in (‘Decimal’, ‘Numeric’)
    Then t.Name + ‘(‘ + cast(Ap.precision as varchar) + ‘,’ + cast(Ap.scale as varchar) + ‘)’
    When t.Name in (‘Binary’, ‘VarBinary’,’Char’,’Varchar’,’NChar’,’NVarchar’)
    Then t.Name + ‘(‘ + cast(Case When t.Name in (‘nchar’,’nvarchar’)
    Then Ap.max_length/2
    Else Ap.max_length End as varchar) + ‘)’
    Else t.Name End,
    DataType = t.Name,
    CharacterMaxLen = Case When t.Name in (‘nchar’,’nvarchar’)
    Then Ap.max_length/2
    Else Ap.max_length End,
    NumericPrecision = Ap.precision,
    NumericScale = Ap.scale
    From sys.objects O
    Inner Join
    sys.all_parameters Ap ON O.object_id = Ap.object_id
    Inner Join
    sys.types t ON Ap.system_type_id = t.system_type_id
    And Ap.user_type_id = t.user_type_id
    Where O.type = ‘P’ And
    O.is_ms_shipped = 0
    –SP NAME TESTS
    AND O.name like ‘rpt%’
    –REPORT PARAMETER NAME TESTS
    –AND Ap.name = ‘@NetUser’
    Order By O.name, Ap.parameter_id

    Reply
  • sys.parameters – information_schema is so slow…

    Reply
  • Thanks for this solution.
    What about values. For example if I want to save in log table all procedure executes with parameters.
    create procedure spInsertData @par1 int, @par2 int
    as
    begin
    declare @paraValues nvarchar(max) = ” –??dynamic get all values
    insert into logs (spName, paraValues) values (‘spInsertData ‘, @paraValues)
    end

    Reply

Leave a Reply

Menu