Stored Procedure with optional "WHERE" parameters

One of the easiest ways to accomplish this:

SELECT * FROM table WHERE ((@status_id is null) or (status_id = @status_id))and ((@date is null) or ([date] = @date))and ((@other_parameter is null) or (other_parameter = @other_parameter))

etc.This completely eliminates dynamic sql and allows you to search on one or more fields. By eliminating dynamic sql you remove yet another security concern regarding sql injection.

Create your procedure like this:

CREATE PROCEDURE [dbo].[spXXX]    @fromDate datetime = null,    @toDate datetime = null,    @subCode int = nullasbeginset NOCOUNT ON/* NOCOUNT limits the server feedback on select results record count */SELECT    fields...FROM    sourceWHERE    1=1--Dynamic where clause for various parameters which may or may not be passed in.and ( @fromDate is null or [dateField] >= @fromDate)and ( @toDate is null or [dateField] <= @toDate)and ( @subCode is null or subCode= @leaveTypeSubCode)order by fields...

This will allow you to execute the procedure with 0 params, all params, or any # of params.

This is the style I use:

t-sql

SELECT    *        FROM    table        WHERE     status_id    =    isnull(@status_id ,status_id)     and    date    =    isnull(@date ,date )     and    other_parameter    =    isnull(@other_parameter,other_parameter) 

oracle

SELECT    *        FROM    table        WHERE     status_id    =    nval(p_status_id ,status_id)     and    date    =    nval(p_date ,date )     and    other_parameter    =    nval(p_other_parameter,other_parameter)