Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dynamic Search without dynamic SQL

Tags:

sql

A user can search for a customer by Firstname, LastName and optionally entering a City.

Is it possible to write a SQL that matches on CITY only if the user entered one w/o using dynamic SQL?

CREATE PROCEDURE [dbo].[SearchCustomer]
@FirstName varchar(30) --REQUIRED
@LastName varchar(30)--REQUIRED
@City varchar(30)  --OPTIONAL
AS
SELECT * FROM CUSTOMER C WHERE
C.FirstName = @FirstName AND
C.LastName = @LastName AND
C.City = IsNull(@City, C.City) --This won't Work if CITY is optional in the database
like image 643
Chad Avatar asked Feb 25 '23 21:02

Chad


2 Answers

Try:

(@City is null OR C.City = @City) 
like image 121
Andomar Avatar answered Mar 03 '23 18:03

Andomar


Try this

COALESCE(C.City,"") = COALESCE(@City,C.City,"")

More info about this function here

like image 25
Silx Avatar answered Mar 03 '23 20:03

Silx