Different methods to find out parameters of a stored procedure

One of my fiends asked me if there is an easy to get the parameters used in the stored procedure. He did not want to use SP_HELP stored procedure because it returns two result sets.

Yes. There are atleast two methods I know other than SP_HELP system stored procedure

Assume the following stored procedure

create procedure proc_add_employee
(
@emp_id int,
@emp_name varchar(40),
@dob date,
@gender char(1),
@address varchar(100)
)
as
Begin
insert into employee(emp_id,emp_name,dob,gender,address)
select @emp_id,@emp_name,@dob,@gender,@address
End

Now you can use the below codes to get the list of parameters used.

1 Use INFORMATION_SCHEMA.PARAMETERS view

select parameter_name, data_type, character_maximum_length from information_schema.parameters
where specific_name='proc_add_employee'

The result is


parameter_name data_type  character_maximum_length
-------------- ---------  -------------------------
@emp_id        int        NULL
@emp_name      varchar    40
@dob           date       NULL
@gender        char       1
@address       varchar    100

2 Use SP_SPROC_COLUMNS system stored procedures

EXEC SP_SPROC_COLUMNS ‘proc_add_employee’

The result is

result

Let me know if you know any other methods

 

Advertisements

2 thoughts on “Different methods to find out parameters of a stored procedure

  1. This is another more conventional way of getting the same info

    SELECT OBJECT_NAME(s.object_id) AS ObjName,
    p.name AS ParamName,
    t.name AS DataType,
    p.max_length AS MaxLength,
    p.precision AS Precision,
    p.scale AS Scale
    FROM sys.parameters p
    INNER JOIN sys.procedures s
    ON s.object_id = p.object_id
    INNER JOIN sys.types t
    ON t.user_type_id = p.user_type_id

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s