What is COALESCE ?? Hi, Walter,
I'll try to respond to your other points later, but I wanted to answer this one.
A basic example of COALESCE: suppose there's a filter screen with entry prompts for name, address, city, state, zip. The user might enter 2 of the 5 (name and zip) and leave the other ones blank. The stored proc has five parms, and returns the results based on what the user filled out.
COALESCE returns the first non-null expression among the arguments, so it's helpful when some of the parms are NULL.
CREATE PROCEDURE GetCustomerData
@name varchar(40) = NULL,
@address varchar(40) = NULL,
@city varchar(40) = NULL,
@state varchar(5) = NULL,
@zip varchar(15)=NULL
AS
SELECT * FROM Customer
WHERE ISNULL(name,'') = COALESCE(@name, ISNULL(name,''))
WHERE ISNULL(address,'') = COALESCE(@address, ISNULL(address,''))
WHERE ISNULL(city,'') = COALESCE(@city, ISNULL(city,''))
WHERE ISNULL(state,'') = COALESCE(@state, ISNULL(state,''))
WHERE ISNULL(zip,'') = COALESCE(@zip, ISNULL(zip,''))
Kevin
