Search database with multiple options

Question

I use SQL Server 2008 Express with Advanced Services. I have a view:

IF EXISTS (select * from sys.views where object_id = object_id(N'[dbo].[vw_PersonDetails]'))
    DROP VIEW vw_PersonDetails
GO

CREATE VIEW vw_PersonDetails
AS
    SELECT
        p.PersonID, p.Title, 
        p.FirstName, p.LastName,
        a.AddressLine1, a.AddressLine2, a.AddressLine3, a.AddressLine4,
        a.Country, a.PostalCode, 
        a.PhoneNumber, a.Email, p.EntryDate
    FROM  
        [dbo].[Persons] p
    INNER JOIN 
        [dbo].[Address] a ON p.PersonID = a.PersonID
GO

Now I have to search this view with each of the columns as options.

Ex:

IF (@firstName != NULL OR @firstName != '') AND 
   (@lastName != NULL OR @lastName != '') AND 
   (@addressLine1 != NULL OR @addressLine1 != '') AND 
   (@postalCode != NULL OR @postalCode != '') AND 
   (@country != NULL OR @country != '') AND
   (@phoneNumber != NULL OR @phoneNumber != '') AND 
   (@email != NULL OR @email != '') AND 
   (@entryDate != NULL)
BEGIN
    SELECT * 
    FROM dbo.vw_PersonDetails 
    WHERE 
        (FirstName LIKE [dbo].[GetSearchString](@firstName) OR
        LastName LIKE [dbo].[GetSearchString](@lastName) OR
        AddressLine1 LIKE [dbo].[GetSearchString](@addressLine1) OR
        Country LIKE [dbo].[GetSearchString](@country) OR
        PostalCode LIKE [dbo].[GetSearchString](@postalCode) OR
        PhoneNumber LIKE [dbo].[GetSearchString](@phoneNumber) OR
        Email LIKE [dbo].[GetSearchString](@email) OR
        EntryDate = @entryDate
       )
END

Now is there any other options except writing a never ending IF-ELSE-IF trail in a stored procedure or building query dynamically. Please help.

Another question is that what will be better: Writing such a stored procedure or making a dynamic query from code.

Thanks in advance.

user816098 · Accepted Answer · 2011-07-16 06:25:16Z

1

You can do it in one simple query:

SELECT * from dbo.vw_PersonDetails WHERE (
    (@firstName IS NULL OR @firstName = '' OR FirstName like @firstName) AND
    ... (same thing for other parameters)

This will work due to SQL Server being intelligent enough to short circuit the evaluation in the expected way. However, this is not actually guaranteed by the specification.

If you want to be on the safe side, you can force evaluation order by doing this:

SELECT * from dbo.vw_personDetails WHERE (
    (CASE 
        WHEN @firstname IS NULL THEN 1
        WHEN @firstname='' THEN 1
        WHEN FirstName like @firstName THEN 1
        ELSE 0
     END=1) AND
    .... (same thing for other parameters)

answered Jul 16, 2011 at 6:25

user816098

2624 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CristiC · Accepted Answer · 2011-07-16 06:25:06Z

You need to have all of the SearchString filled for your query to run (because of the AND condition in your IF statement).

You can create a dynamic query to run no matter if the parameters are passed or not:

DECLARE @sqlCommand VARCHAR(MAX)
SELECT @sqlCommand = 'SELECT * FROM dbo.vw_PersonDetails WHERE 1=1'

IF  (@firstName != NULL OR @firstName != '') SELECT @sqlCommand = @sqlCommand + ' AND FirstName LIKE [dbo].[GetSearchString](@firstName)'
IF  (@lastName != NULL OR @lastName != '') SELECT @sqlCommand = @sqlCommand + ' AND LastName LIKE [dbo].[GetSearchString](@lastName)'
IF  (@addressLine1 != NULL OR @addressLine1 != '') SELECT @sqlCommand = @sqlCommand + ' AND AddressLine1 LIKE [dbo].[GetSearchString](@addressLine1)'

.....

EXEC (@sqlCommand)

Collectives™ on Stack Overflow

Search database with multiple options

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related