Question: I want to build a single select stored procedure for SQL 2005 that is universal for any select query on that table. 

```
**Columns**
LocationServiceID
LocationID
LocationServiceTypeID
ServiceName
ServiceCode
FlagActive

```

For this table I may need to select by LocationServiceID, or LocationID, or LocationServiceTypeID or ServiceName or a combination of the above. 

I'd rather not have a separate stored procedure for each of them. 

I assume the best way to do it would be to build the 'WHERE' statement on NOT NULL. Something like

```
SELECT * FROM LocationServiceType WHERE

  IF @LocationID IS NOT NULL (LocationID = @LocationID)
  IF @LocationServiceID IS NOT NULL (LocationServiceID = @LocationServiceID)
  IF @LocationServiceTypeID IS NOT NULL (LocationServiceTypeID = @LocationServiceTypeID)
  IF @ServiceName IS NOT NULL (ServiceName = @ServiceName)
  IF @ServiceCode IS NOT NULL (ServiceCode = @ServiceCode)
  IF @FlagActive IS NOT NULL (FlagActive = @FlagActive)

```

Does that make sense?

Answer: 
First of all, your code will not work. It should look like this:

```
SELECT * FROM LocationServiceType WHERE
(@LocationID IS NULL OR (LocationID = @LocationID)
... -- all other fields here

```

This is totally valid and known as 'all-in-one query'. But from a performance point of view this is not a perfect solution as soon as you don't allow SQL Server to select optimal plan. You can see more details [here](http://blogs.msdn.com/bartd/archive/2009/05/03/sometimes-the-simplest-solution-isn-t-the-best-solution-the-all-in-one-search-query.aspx). 

Bottom line: if your top priority is 'single SP', then use this approach. In case you care about the performance, look for a different solution.
What I've always done is is set the incoming parameters to null if should be ignored in query
then check variable for null first, so if variable is null condition short circuits and filter is not applied. If variable has value then 'or' causes filter to be used. Has worked for me so far.

```
SET @LocationID = NULLIF(@LocationID, 0)
SET @LocationServiceID = NULLIF(@LocationServiceID, 0)
SET @LocationServiceTypeID = NULLIF(@LocationServiceTypeID, 0)

    SELECT * FROM LocationServiceType WHERE

      (@LocationID IS NULL OR LocationID = @LocationID)
     AND (@LocationServiceID IS NULL OR LocationServiceID = @LocationServiceID)
     AND (@LocationServiceTypeID IS NULL OR @LocationServiceTypeID = @LocationServiceTypeID)

```

etc...