SQL StoredProcedure with non required parameters

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),
开发者_JS百科
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!

---

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)

---

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

---

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