Passing the report parameters into stored procedure

When you are designing a screen which allows the user to choose the period, multiple customer types, etc, you might have a hard time in passing the values to the stored procedure. The common way to achieve this by having a nvarchar(max) parameter type to cater for the multiple customer types. The simplest way to handle multiple values passing by the caller is splitting the incoming string into table variable.

For example, the user want to get the sales report for the selected customer types (corporate customers, travel agents and walk-in customers) for year 2011. Then, you might have something similar as shown below:

exec pr_rpt_sales_by_cust_type
  @start_date = '2011-01-01',
  @end_date = '2011-12-31',
  @customer_type = 'corporate, travel agent, walkin customer'

Assuming that we are using the display text to filter the data to ease the explanation. In real live, you might have to pass in the foreign key value.

Also, you need another function or stored procedure to parse the @customer_type parameter into table variable which you might have to do some research. ;)

Ok. Back to this topic. The select statement will be something like this:

select customer_type, sum(sales_amount) as total_sales_amount
from vw_sales
where transaction_date between @start_date and @end_date
  and (customer_type in (
      select customer_type
      from dbo.fn_parse_param_to_table(@customer_type))
    or @customer_type = ''
  )
group by customer_type

Note: "fn_parse_param_to_table" is the function that you have to develop it.