Stored Proc with optional parameters

Total article views: 158 | Views in the last 30 days: 5

 Rate this |   Join the discussion |  Briefcase

Sometimes people want a Stored Proc with optional parameters. A lot of times people will use a coalesce for this. The problem with optional paremeters is that the query plan will never be an exact mach for that specific query.

( You never know what paremeters are filled..... )

To solve this problem look at the following script. It will generate a query with the parameters filled that you have filled.

It is much faster than a coalesce variant. And the more parameters you add, the better this one gets.......

Some Advantages :
1. Very Easy to maintain
2. ALWAYS a good query plan
3. Add as much optional parameters as you want,
the resulting query will always be an exact mach of what YOU want.....
4. Beats a COALESCE by FAR..... ( 10 to 30 times faster )

Here is the example......

Total article views: 158 | Views in the last 30 days: 5