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: 
here is the most extensive article I've ever seen on the subject:

[Dynamic Search Conditions in T-SQL by Erland Sommarskog](http://www.sommarskog.se/dyn-search-2005.html)

here is an outline of the article:

> 
> 
> ```
>  Introduction
>       The Case Study: Searching Orders
>       The Northgale Database
>    Dynamic SQL
>       Introduction
>       Using sp_executesql
>       Using the CLR
>       Using EXEC()
>       When Caching Is Not Really What You Want
>    Static SQL
>       Introduction
>       x = @x OR @x IS NULL
>       Using IF statements
>       Umachandar's Bag of Tricks
>       Using Temp Tables
>       x = @x AND @x IS NOT NULL
>       Handling Complex Conditions
>    Hybrid Solutions – Using both Static and Dynamic SQL
>       Using Views
>       Using Inline Table Functions
>    Conclusion
>    Feedback and Acknowledgements
>    Revision History
> 
> ```
> 
>
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...