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
IF @StartDate IS NULL and @EndDate IS NULL and @UserId IS NULL
SELECT * FROM rm_exchange
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),
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 + ''' '
set @criteria = criteria + ' and UserID = ''' + @UserID + ''' '
If @AccNo is not null
if @criteria is null
set @criteria = ' where AccNo = ''' + @AccNo + ''' '
set @criteria = ' and AccNo = ''' + @AccNo + ''' '
-- By now all your parameters have been handled.
if @criteria is not null
set @query = @query + @criteria
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.