Technical Article

Stored Proc with optional parameters

,

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......



CREATE PROCEDURE dbo.OptionalParemeters
(
@Field1 int = null, /* optional variable */@Field2 varchar(64) = null, /* optional variable */@Field3 varchar(16) = null, /* optional variable */)
AS
BEGIN 
Declare @ColumnStr varchar(600)
Declare @FromStr varchar(100)
Declare @JoinStr varchar(500)
Declare @WhereStr varchar(2000)
Declare @SQLStatement nvarchar(3000)
-- Define the columns to display
Set @ColumnStr = '
m.Field1,
m.Field2,
m.Field3,
J.FieldA,
J.FieldB
'
-- Define from table
Set @FromStr = '
Table1 m
'
-- Define the joined tables
Set @JoinStr = '
Left join Table1 J on M.ID=J.ID
'
-- Empty the where clause
Set @WhereStr = ''

if @Field1 is not NULL -- Is this parameter filled ?
begin 
if rtrim(@WhereStr) <> '' set @WhereStr = @WhereStr + ' and ' -- do we have to add ' AND '
set @WhereStr = @WhereStr + ' m.Field1 =' + str(@pv_field1)
end

if @Field2 is not NULL -- Is this parameter filled ?
begin 
if rtrim(@WhereStr) <> '' set @WhereStr = @WhereStr + ' and ' -- do we have to add ' AND '
set @WhereStr = @WhereStr + ' m.Field2=' + char(39)+ @Field2 +char(39)
end

if @Field3 is not NULL -- Is this parameter filled ?
begin 
if rtrim(@WhereStr) <> '' set @WhereStr = @WhereStr + ' and ' -- do we have to add ' AND '
set @WhereStr = @WhereStr + ' m.Field2=' + char(39)+ @Field3 +char(39)
end


if rtrim(@WhereStr) <> '' set @Wherestr = +' Where '+ @WhereStr

set @SQLStatement = 'Select '+ @ColumnStr + ' From ' + @FromStr + ' ' + @Joinstr + @WhereStr 
EXEC sp_executesql @SQLStatement
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating