IF ELSE statement in WHERE clause

actually i need to do a sql report... there are 3 parameters (date from. date to, issue_user_id) that are required in order to retrieve the data...now i am facing one problem, if i just enter the date_from and date_to, and leave the issue_user_id blank, i wish to retrieve all the data, but i can't do it.

i have try to use:

if @.IssueUserID = ' ' Select...(blah blah ) else select (blah blah)...it is long and work in Query analyzer but no working in .NET Reporting services... :confused: is it possible to enter IF ..ELSE statement in WHERE clause?hope that u all can help....

================================================== ==
SELECT REPLACE(REPLACE(REPLACE(REPLACE(tbl_Receipt.Paymen tRefID, '1', 'Deposit for Admission'), '2', 'Consultation Services'), '3', 'Medical Fees'), '4','Others') AS PaymentRefID, tbl_Receipt.ReferenceNo, tbl_Receipt.ReceiptDate, CONVERT(varchar(10), tbl_Receipt.ReceiptDate, 103) AS ReceiptDt, tbl_Receipt.TotalAmountPaid, tbl_Receipt.Payee, tbl_ReceiptDT.PaymentTypeID, tbl_PaymentType.PaymentType, tbl_ReceiptDT.Amount,
tbl_User.Name, tbl_Receipt.IssueUserID, tbl_User.Designation
FROM tbl_Receipt WITH (nolock) INNER JOIN
tbl_ReceiptDT WITH (nolock) ON tbl_Receipt.ReceiptID = tbl_ReceiptDT.ReceiptID INNER JOIN
tbl_PaymentType WITH (nolock) ON tbl_ReceiptDT.PaymentTypeID = tbl_PaymentType.PaymentTypeID INNER JOIN
tbl_User ON tbl_Receipt.IssueUserID = tbl_User.UserID

WHERE (tbl_Receipt.ReceiptDate BETWEEN CONVERT(datetime, @.DateFrom, 103) AND CONVERT(datetime, @.DateTo, 103)) AND
(tbl_Receipt.IssueUserID = @.IssueUserID)
ORDER BY tbl_User.UserID, tbl_ReceiptDT.PaymentTypeIDHi,

A trick I have used in this case would be to code:

...
AND (tbl_Receipt.IssueUserID =
ISNULL(@.IssueUserID, tbl_Receipt.IssueUserID )
)
...

In other words, if @.IssueUserId is not present, you join the column to itself, which gets all the data...

I've done this in stored procedure reports where I can default the passed parameters, e.g. :

create proc myproc (... , @.IssueUserID int = NULL, ...)

HTH,

Bill|||thank you for ur help... :rolleyes:

but when i try to edit the queries, i won't get all the data when the IssueUserID is blank. :(|||thank you for ur help... :rolleyes:

but when i try to edit the queries, i won't get all the data when the IssueUserID is blank. :(

so @.IssueUserID is a character data type, and when it's "blank" you don't want it to be considered in the where clause...?

try

AND ( (@.IssueUserID = ' ') OR (@.IssueUserID != ' ' AND table.column = @.IssueUserID))

HTH

Bill|||;) actually i really appreciate your help. the way u teach is working when i try in SQL Query Analyzer. When comes to the MS.NET reporting, the query is not working..so sad. :cool:|||Very confusing here whether you are checking for NULLs, zero-length strings ('') or values with just a single space (' '). Your MS.NET reporting layer may be interpreting things differently than Query Analyzer.

Your code indicates a single space, but that is an unusual value to be submitted by an interface to indicate the absence of data.

This code handles both NULL values and zero-length strings:

AND (nullif(@.IssueUserID, '') is null OR (nullif(@.IssueUserID, '') is not null AND table.column = @.IssueUserID))