Stored Procedures with optional parameters

/* create the example table */
CREATE TABLE customer(
    id  int not null,
    first   nvarchar(200),
    last    nvarchar(200),
    city    nvarchar(200),
    state   nvarchar(40),
    zip     nvarchar(10),
    country nvarchar(100)
    PRIMARY KEY(id),
    UNIQUE(id)
)

/* populate some test data */
insert into customer values(1, 'Joe', 'Public', 'Seattle', 'WA', '98144', 'USA')
insert into customer values(2, 'Jane', 'Public', 'Amsterdam', 'BT', '1063', 'NLD')
insert into customer values(3, 'Jennifer', 'Public', 'Miami', 'FL', '33101', NULL)

/* create the stored procedure (the wrong way) */
CREATE PROC retrieve_customers_by_location(
                @city   NVARCHAR(100),
                @state NVARCHAR(2),
                @zip NVARCHAR(9),
                @country NVARCHAR(10)
        )
        As

        Begin

        select * from customer where city=@city and state=@state and zip=@zip and country = @country

        End

/* Confirm that this doesn't work as expected by passing in only one or two parameters (these all return nothing) */
exec retrieve_customers_by_location null,null,null,null
exec retrieve_customers_by_location null,null,null,'USA'
exec retrieve_customers_by_location null,'BT',null,null
exec retrieve_customers_by_location 'Seattle','WA',null,null

/* so what will return a result? only those with all parameters supplied, which isn't very useful */
exec retrieve_customers_by_location 'Seattle','WA','98144','USA'

/* alter the stored procedure according to another blog, this improves it a lot but it has a huge gotcha! */
ALTER PROC retrieve_customers_by_location(
                @city   NVARCHAR(100),
                @state NVARCHAR(2),
                @zip NVARCHAR(9),
                @country NVARCHAR(10)
        )
        As

        Begin

        select * from customer where city = ISNULL(@city,city) and state = ISNULL(@state,state) and zip=ISNULL(@zip,zip) and country=ISNULL(@country,country)

        End

/* The gotcha is that this will always exclude any rows in the database with a null value in any of the columns.
    Notice that Jennifer Public is never returned, even though she should match the first query and the last.
 */
exec retrieve_customers_by_location null,null,null,null
exec retrieve_customers_by_location null,null,null,'USA'
exec retrieve_customers_by_location null,'BT',null,null
exec retrieve_customers_by_location 'Seattle','WA',null,null
exec retrieve_customers_by_location null,null,'33101',null


/* now alter the stored procedure...this finally works */
ALTER PROC retrieve_customers_by_location(
                @city   NVARCHAR(100),
                @state NVARCHAR(2),
                @zip NVARCHAR(9),
                @country NVARCHAR(10)
        )
        As

        Begin

        select * from customer
                where
                (city = @city or @city is null) and
                (state= @state or @state is null) and
                (zip = @zip or @zip is null) and
                (country = @country or @country is null)

        End


/* Retry those queries from above, and voila, works as expected */
exec retrieve_customers_by_location null,null,null,null
exec retrieve_customers_by_location null,null,null,'USA'
exec retrieve_customers_by_location null,'BT',null,null
exec retrieve_customers_by_location 'Seattle','WA',null,null

/* now try this */
exec retrieve_customers_by_location

/* It returns something like "...expects parameter...which was not supplied." right? */

/* So make the stored procedure even better, by making the parameters optional (or in other words supply default parameters),
no more need to pass in null when the value is not supplied */
ALTER PROC retrieve_customers_by_location(
                @city   NVARCHAR(100) = null,
                @state NVARCHAR(2) = null,
                @zip NVARCHAR(9) = null,
                @country NVARCHAR(10) = null
        )
        As

        Begin

        select * from customer
                where
                (city = @city or @city is null) and
                (state= @state or @state is null) and
                (zip = @zip or @zip is null) and
                (country = @country or @country is null)

        End


/* now you can issue this query, and it will return all results */
exec retrieve_customers_by_location