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
Try:
(@City is null OR C.City = @City)
Try this
COALESCE(C.City,"") = COALESCE(@City,C.City,"")
More info about this function here
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With