asked on

MS SQL LIKE not returning all....

Hello,

I have the attached Stored Procedure --

CREATE PROCEDURE [dbo].[QueryPartnerReport] 
@firstname nvarchar(50),
@lastname nvarchar(50),
@tradingname nvarchar(50),
@golfclub nvarchar(50),
@town nvarchar(50),
@county nvarchar(50),
@country nvarchar(50),
@clubtype nvarchar(5),
@tuition nvarchar(1),
@accounttype nvarchar(5),
@rda nvarchar(5),
@profits char(1),
@vat char(1),
@retainer char(1)
AS
BEGIN
SET NOCOUNT ON;
IF @accounttype = 99
Begin
SELECT * 
FROM dbo.PartnerListComplete
WHERE
FirstName like '%'+@firstname+'%' AND
LastName like '%'+@lastname+'%' AND
TradingAs like '%'+@tradingname+'%' AND
Clubname like '%'+@golfclub+'%'  AND
Town like '%'+@town+'%' AND 
County like '%'+@country+'%' AND
Country like '%'+@country+'%' AND
(ClubTypeID like @clubtype OR @clubtype IS NULL) AND
(Tuition like @tuition OR @tuition IS NULL) AND
AccountTypeID in (1,2) AND
(RDAID like @RDA OR @RDA IS NULL) AND
(Profits like @profits OR @profits IS NULL) AND
(VAT like @vat OR @vat IS NULL) AND
(RetainerSurvey LIKE @retainer OR @retainer IS NULL)
ORDER BY AccountType, LastName, FirstName 
END
ELSE
Begin
SELECT * 
FROM dbo.PartnerListComplete
WHERE
FirstName like '%'+@firstname+'%' AND
LastName like '%'+@lastname+'%' AND
TradingAs like '%'+@tradingname+'%' AND
Clubname like '%'+@golfclub+'%'  AND
Town like '%'+@town+'%' AND 
County like '%'+@country+'%' AND
Country like '%'+@country+'%' AND
(ClubTypeID like @clubtype OR @clubtype IS NULL) AND
(Tuition like @tuition OR @tuition IS NULL) AND
AccountTypeID in (1,2,3,4,5,6,7,9) AND 
(AccountTypeID like @accounttype OR @accounttype IS NULL) AND
(RDAID like @RDA OR @RDA IS NULL) AND
(Profits like @profits OR @profits IS NULL) AND
(VAT like @vat OR @vat IS NULL) AND
(RetainerSurvey LIKE @retainer OR @retainer IS NULL)
ORDER BY AccountType, LastName, FirstName
END
END

GO

Open in new window

The default values being sent by my application for -
FirstName
LastName
TradingAs
Clubname
Town
County
Country

is %

The issue is, if only the defaul values are sent, it only returns records with values in the columns. For instance doing a search with just default values, will not return any of the records that have NULL values in any of -

FirstName
LastName
TradingAs
Clubname
Town
County
Country

How do I get around this?

Thanks

ASKER CERTIFIED SOLUTION

Thomasian

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

PortletPaul

>>will not return any of the records that have NULL values in any of

WHERE
(
(@firstname IS NOT NULL AND FirstName like '%'+@firstname+'%' )
OR
( @firstname IS NULL)
)
AND
(
(@lastnameIS NOT NULL AND lastnamelike '%'+@lastname+'%' )
OR
( @lastnameIS NULL)
)
...

garethtnash

ASKER

Thank you

PortletPaul

if your parameters are null ='%' won't work :(
you specifically said NULL

you may have to set every parameter to an empty string for that answer to work
NULL cannot be evaluated by the equal operator

PortletPaul

please try it here at http://sqlfiddle.com/#!3/bd668/4

the accepted solution will fail if your parameters are NULL

Thomasian

What garethtnash meant is that when any of the FIELDS has a value of null, no records are returned even if the search paramenter is set to the default value ('%') .

Thomasian

the accepted solution will fail if your parameters are NULL

The parameters are not set to null. The default value is '%' as stated in the question

PortletPaul

Cool - then it is fine

no offence meant Thomasian, highest respects

Thomasian

No problem.

Btw, you can remove the statement "@firstname IS NOT NULL" from your query.

PortletPaul

:) that depends, there are circumstances that as soon as that is evaluated (and returns false) the remainder is not evaluated - which is the expensive bit)

i.e. depends on the optimizer (type/version), and I'd rather not leave it to chance. It is of infinitesimal overhead but great potential gain

plus: I too old to learn too many new tricks