CODEWITHSUNDEEP
t-sqlstored-proceduresselectconditional-statements
Redtopia
Redtopia

Reputation: 5237

How to write a conditional SELECT query in TSQL using arguments in the WHERE/AND clause?

I've got a stored procedure that returns postal codes within a specified radius. The arguments are

ALTER PROCEDURE [dbo].[proximitySearch] 
    @proximity int = 0,
    @country varchar (2) = NULL,
    @city varchar (180) = NULL,
    @state varchar (100) = NULL,
    @stateAbr varchar (2) = NULL,
    @postalCode varchar(20) = NULL
AS...

In the proc, the first query needs to select a single record (or no record) that matches whatever was passed in and assign the lat/long to local variables, as I started to write below:

SELECT TOP 1 @Longitude = Longitude, @Latitude = Latitude
FROM   PostalCodes
WHERE  ...

This is where I get stumped... the WHERE clause needs to be conditional based on what was passed in. Some arguments (or all of them) can be NULL, and I don't want use them in the query if they are.

I was thinking along the lines of:

SELECT TOP 1 @Longitude = Longitude, @Latitude = Latitude
FROM   PostalCodes
WHERE Longitude IS NOT NULL
AND CASE WHEN @postalCode IS NOT NULL THEN PostalCode = @postalCode ELSE 1 END

...but this doesn't work. How is something like this typically done? (I'm definitely not a seasoned TSQL guy!!!) Thanks in advance!

Upvotes: 2

Views: 2498

Answers (2)

Andriy M
Andriy M

Reputation: 77657

There is more than one way of implementing that kind of logic:

1)

SELECT TOP 1 @Longitude = Longitude, @Latitude = Latitude
FROM   PostalCodes
WHERE Longitude IS NOT NULL
AND (@postalCode IS NULL OR PostalCode = @postalCode)

2)

SELECT TOP 1 @Longitude = Longitude, @Latitude = Latitude
FROM   PostalCodes
WHERE Longitude IS NOT NULL
AND PostalCode = COALESCE(@postalCode, PostalCode)

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300499

SELECT TOP 1 @Longitude = Longitude, @Latitude = Latitude
FROM   PostalCodes
WHERE 
      ((@postalCode IS NULL) OR (PostalCode = @postalCode))
  AND ((@someotherparam IS NULL) OR (someothercolumn = @someotherparam))  etc...

But be aware that this technique can suffer from 'parameter sniffing'

Upvotes: 2

Related Questions