ISNULL + Wildcard problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ISNULL + Wildcard problems

Author

Topic

james_w
Starting Member

21 Posts

Posted - 2009-09-03 : 13:28:54

I'm having problems returning results from a query that checks to see if some variables are NULL, if they are they should use the '%' wildcard and return everything for those columns but it doesn't return anything, my SQL is below:

DECLARE @sName nvarchar(70)
DECLARE @sTown nvarchar(30)
DECLARE @sCounty nvarchar(30)

SET @sName = 'James'
SET @sTown = NULL
SET @sCounty = NULL

PRINT @sTown
PRINT @sCounty

SELECT
[SupplierID]
,[Name]
,[Address]
,[Area]
,[Area2]
,[Town]
,[County]
,[PostCode]
,[Country]

FROM [EstateAgents]
WHERE [Name] LIKE '%'+ISNULL(@sName,'')+'%'
AND [Town] = ISNULL(@sTown,'%')
AND [County] = ISNULL(@sCounty,'%')

ORDER BY [Name], [Town], [County]

Can anybody see where i'm going wrong or if it is possible?

Thanks for any help in advance.

James.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-09-03 : 13:51:23

not sure what you want your results to be...but if you change the AND to OR in your WHERE predicate, that might give you the data you're looking for.

james_w
Starting Member

21 Posts

Posted - 2009-09-03 : 14:01:21

This is going to be run when a user fills in a search form, so if they fill in [Name], [Town] and [County] then I want it to filler for those results not for [Name] OR [Town] OR [County] as that would produce loads of results for the user and could spend ages searching for the one they are after so it needs to be AND

Also if the user only fills in the [Name] field i would want the query to run the wildcard for [Town] and [County].

Hopefully that makes sense.

Thanks
James.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-03 : 14:16:28

It looks like you want a "Catch-All-Query." Here is one of many links:
[url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

Here is a change to your WHERE clause the should work, but read the article so you understand the implications of such a query.

FROM [EstateAgents]
WHERE ([Name] LIKE '%' + @sName + '%' OR @sName IS NULL)
AND ([Town] = @sTown OR @sTown IS NULL)
AND ([County] = @sCounty OR @sCountry IS NULL)

james_w
Starting Member

21 Posts

Posted - 2009-09-03 : 14:38:21

Thanks Lamprey, that works for me.

The table isn't all that big and takes no time at all to run, suppose that'll will have to do till i have more time to do as the article you posted suggest.

Thanks very much for that, was helpful and an interesting article.

James.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources