Learn AspNet Learn AspNet - 4 months ago 10
SQL Question

Exclude column in where clause if field value is empty

I have a stored procedure for search screen where I have 5 different filters.
Users can leave the filters empty or have one filter and click on search button.

All the data is coming from database, I am trying to create stored procedure which can exclude columns in where clause if the parameter value is empty string.

@Id as nvarchar(256) = 1
@blnIsinProgress bit = 0
@strStatus varchar(20) = ''
@strName varchar(50) = ''
@strConfirmationNumber varchar(50) = 123
@dtmSubmittedFrom Date = '12/31/9999'
@dtmSubmittedTo Date = '12/31/9999'
as
BEGIN
SELECT *
FROM tblTable
WHERE
(@Id IS NULL OR lngID = @Id) AND
(@blnIsinProgress IS NULL OR blnIsinProgress = @blnIsinProgress) AND
(@strStatus = '' OR strStatus = @strStatus) AND
(@strName= '' OR strName= @strName) AND
(@strConfirmationNumber = '' or @strConfirmationNumber = @strConfirmationNumber )
End


But

Execute spManage 1,0,'','',123


will give me all the results

Answer

The problem is this line:

(@strConfirmationNumber = '' or @strConfirmationNumber = @strConfirmationNumber )

The second condition is always true. You have an extra @. So, try this:

(@strConfirmationNumber = '' or @strConfirmationNumber = strConfirmationNumber )
Comments