Different methods to get parameter list of a stored procedure

There was a question in the forum

I was hoping to find an easy way to get a parameter list of a stored procedures parameters. If the procedure has 3 paramaters, I want a list like this:

param1
param2
param3

We will see how to do this post. Consider the following objects

create table products
(
	product_name varchar(100),
	uom varchar(10),
	exp_date datetime,
	category varchar(100),
	qoh int
);

Create the procedure to add data to that table

create procedure proc_add_products
(
	@product_name varchar(100),
	@uom varchar(10),
	@exp_date datetime,
	@category varchar(100),
	@qoh int
)
as
Begin
insert into product_details 
(product_name,uom,exp_date,category,qoh)
select @product_name,@uom,@exp_date,@category,@qoh
end

Now all the parameter names should be listed out.

There are many methods to do this in sql.

1 Use INFORMATION_SCHEMA.PARAMETERS system catalog view

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

2 Use sys.parameters system object catalog view

select 
      t1.name as PARAMETER_NAME, t2.name as data_type 
from 
      sys.parameters as t1 inner join sys.types as t2
      on t1.system_type_id=t2.system_type_id
where 
      object_id=object_id('proc_add_products') 
order by 
      t1.parameter_id

3 Use sys.parameters system object catalog view

select 
      t1.name as PARAMETER_NAME, t2.name as data_type 
from 
      sys.all_parameters as t1 inner join sys.types as t2
      on t1.system_type_id=t2.system_type_id
where 
      object_id=object_id('proc_add_products') 
order by 
      t1.parameter_id

The first three methods will return the following result

PARAMETER_NAME DATA_TYPE
------------------------ -----------------
@product_name varchar
@uom varchar
@exp_date datetime
@category varchar
@qoh int

4 Use sp_help system Stored Procedure

EXEC sp_help proc_add_products

Result

result1

5 Use sp_procedure_params_rowset system Stored Procedure

EXEC sp_procedure_params_rowset proc_add_products

Result

result2

6 Use sp_sproc_columns system Stored Procedure

EXEC sp_sproc_columns proc_add_products

Result

result3

Advertisements

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