search condition

[solomon_13000]

How can I ensure that the stored procedure below is able to use either one or all the parameters to filter records from a table?

CREATE PROCEDURE ABC
@StartDate datetime = null,
@EndDate datetime = null,
@UserId varchar(255) = null,
@AccNo varchar(255) = null
AS
BEGIN
IF @StartDate IS NULL and @EndDate IS NULL and @UserId IS NULL
BEGIN
SELECT * FROM rm_exchange
ELSE
BEGIN
END
END

Your help is kindly appreciated.

Thank You.

[debasisdas]

What you want to do ?

use either one or all the parameters to filter records ?

[solomon_13000]

Both as in either one of all the parameters to filter records.

[adam_k]

You can easily do it with dynamic SQL.
This means that you will write your query on the fly, depending on the parameters or conditions you find along the way.
In your case I think this would mean:

declare @query varchar(max),
@criteria varchar(max)

set @query= 'select * from rm_exchange ' 

If @EndDate is not null 
If @StartDate is not null 
set @criteria = ' where date_field between ''' + @StartDate + ''' and ''' + @EndDate
else -- Only EndDate
set @criteria = ' where date_field <= ''' + @EndDate + ''' ' 


if @StartDate is not null and @EndDate is null 
set @criteria = ' where date_field >= ''' + @StartDate + ''' '


if @UserId is not null 
if @criteria is null -- Check for possibility that no dates criteria have been passed to the sproc
set @criteria = ' where UserID = ''' + @UserID  + ''' ' 
else 
set @criteria = criteria + ' and UserID = ''' + @UserID + ''' ' 

If @AccNo is not null 
if @criteria is null 
set @criteria = ' where AccNo = ''' + @AccNo + ''' ' 
else 
set @criteria = ' and AccNo = ''' + @AccNo + ''' ' 

-- By now all your parameters have been handled. 

if @criteria is not null 
set @query = @query + @criteria

Exec(@query)

I just typed this in here without giving this a test, so there might be typos and other errors. Also I'm having my morning coffee, expect errors and typos again.
Anyway this will give you a hint.

[solomon_13000]

Thank you for the hint :)