Posted by Tony Rogerson on 02/05/06 13:52

Thats true, and true of SQL generally.

There are ways to specify a RECOMPILE though.

Tony.

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97617176DBCC3Yazorman@127.0.0.1...
> Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes:
>> create proc myproc
>> @optional_parm1 int = NULL,
>> @optional_parm2 int = NULL
>> begin
>> if @optional_param1 is not null and @optional_param2 is not null
>> select max( blah )
>> from yourquery (or view)
>> where col1 = @optional_parm1
>> and col2 = @optional_parm2
>> else if @optional_param1 is not null
>> select max( blah )
>> from yourquery (or view)
>> where col1 = @optional_parm1
>> else if @optional_parm2 is not null
>> select max( blah )
>> from yourquery (or view)
>> where col2 = @optional_parm2
>> else
>> select max( blah )
>> from yourquery (or view)
>>
>> end
>>
>> While the above does contain logic, it will give you the best plan in MS
>> SQL Server,
>
>
> Maybe. Keep in mind that SQL Server employs parameter sniffing, so if
> you first call the procedure with NULL in the input parameters, all
> the SELECT:s will be optimized for NULL, which may not give the best
> plan.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: How to go around the SQL Server 2005 User Instance problem when using Remote Desktop
• Prev in forum: Re: Views vs Stored Procedures, whats the difference?
• Thread view: Re: Views vs Stored Procedures, whats the difference?

[Reply to this message]