coalesce, isnull, etc. in a where clause

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 2000 Forums

Transact-SQL (2000)

coalesce, isnull, etc. in a where clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-18 : 10:45:51

Blair writes "I read Garth Wells hint on unsing the following syntax. Do you know of equivalent syntax that does not cause a table scan?

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
Cus_City = COALESCE(@Cus_City,Cus_City) AND
Cus_Country = COALESCE(@Cus_Country,Cus_Country)"

barmalej
Starting Member

40 Posts

Posted - 2002-01-18 : 11:27:26

Try this:

DECLARE @CityFrom varchar(32), @CityTo varchar(32)
IF @Cus_City IS NULL
BEGIN
SET @CityFrom=''
SET @CityTo='zzzzzzzzz'
END
ELSE
SET @CityFrom=@Cus_City
SET @CityTo=@Cus_City
END
-- And your select then:

SELECT * from YourTable yt
WHERE yt.Cus_City between @CityFrom and @CityTo

blairjensen
Starting Member

1 Post

Posted - 2002-01-18 : 18:46:01

Thanks. You got me excited but, this code still table scans unless one of the columns in the search criteria is the clustered index. In that case it scan's the clustered index in every situation. Any other suggestions?

quote:

Try this:

DECLARE @CityFrom varchar(32), @CityTo varchar(32)
IF @Cus_City IS NULL
BEGIN
SET @CityFrom=''
SET @CityTo='zzzzzzzzz'
END
ELSE
SET @CityFrom=@Cus_City
SET @CityTo=@Cus_City
END
-- And your select then:

SELECT * from YourTable yt
WHERE yt.Cus_City >= @CityFrom and yt.Cus_City <= @CityTo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources