CODEWITHSUNDEEP
mysqlsqlasp.netweb
Learn AspNet
Learn AspNet

Reputation: 1622

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

Upvotes: 0

Views: 772

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270341

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 )

Upvotes: 2

Related Questions