CODEWITHSUNDEEP
c#sqlsql-serversearchstored-procedures
Arnab Das
Arnab Das

Reputation: 3728

Search database with multiple options

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.

Upvotes: 0

Views: 967

Answers (2)

user816098
user816098

Reputation: 262

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)

Upvotes: 1

CristiC
CristiC

Reputation: 22698

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)

Upvotes: 1

Related Questions