SQL StoredProcedure with non required parameters

Question

In my application I have a form that has some filter fields. None are required, if the user does enter a value I need to provide those as parameters in a where clause.

I don't know how to handle this in my stored procedure though.

for example the following stored procedure:

--parameters
@customername varchar(50),

SELECT * from ORDERS
WHERE customername = @customername

so if the user enters a Customername then of course it will return all with that customer name, if the user does not enter a user name it needs to return everything. How can I do this in this statement? is there a wildcard I can pass if the user didn't enter a customer name? Or do I need a separate procedure. I hope that makes sense, thanks!

Answer 1

SELECT * from ORDERS 
WHERE customername = @customername or @customername is null

Answer 2

Try this

--parameters
@customername varchar(50) = NULL,

SELECT * from ORDERS
WHERE customername = COALESCE(@customername, customername)

Per the comment that you need to return customernames that are null, This is integrated with Russ'.

SELECT * from ORDERS
WHERE (customername = COALESCE(@customername, customername)
 OR customername IS NULL)

Answer 3

You could write an if statement in your stored proc. Like this.

IF @customername is null
BEGIN
  SELECT * FROM ORDERS
END
ELSE
BEGIN
  SELECT * FROM ORDERS WHERE customername = @customername
END

Answer 4

See this link for some ideas, as well as the pros and cons of different approaches to conditional where statements:

http://www.sqlservercentral.com/articles/T-SQL/61918/