Default values in stored procedure

Did you know that you can have default values for the parameters of the stored procedure? It is very similar to assigning default value to a column of a table.

Let us consider this dataset

use tempdb

create table product
(
product_id int ,
product_name varchar(100)
)


insert into product (product_id , product_name)
select 1001,'Television' union all
select 1002,'Mobile' union all
select 1003,'Vehicle'

Suppose you want to pass product name and get the details for that product or you do not want to pass any value and list out all details from the table. You can create a procedure with a parameter having default value of NULL as shown below

create procedure proc_list_product
(
@product_name varchar(100)=null
)
as
Begin
select product_id, product_name from product where (product_name = @product_name or @product_name is null)
End

Now see the result for the following procedure calls

EXEC proc_list_product 'mobile'

The result is

product_id   product_name
----------- ---------------------
1002         Mobile


EXEC proc_list_product

The result is

product_id   product_name
----------- ---------------------

1001         Television
1002         Mobile
1003         Vehicle

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