Best approach to this SP 3

[robertfah]

What is the best practice/approach to writing a SP that returns either a single row or all rows based on the 1 parameter passed in?

Lately we've noticed a need to retrieve either 1 record or all records from tables within our DB (Customer(s), Location(s), Room(s), etc.). So for customers, we would have 2 SP's:

SELECT *
FROM Customers

And

SELECT *
FROM Customers
WHERE CustomerID = @CustomerID

We were thinking of combining them into 1 and making the parameter NULL by default, then checking for it:

IF @CustomerID IS NULL
  SELECT *
  FROM Customers
ELSE
  SELECT *
  FROM Customers
  WHERE CustomerID = @CustomerID

The only issue I see with this is if you had a large table and were listing out the fields, there would be the potential for not having the same fields in both SELECT statements.....but other than Dynamic SQL, is there a better way/approach?

 

[markros]

No, this approach is fine, but it's better to not use SELECT *, but explicitly list every field.

If you want to learn more about the problem, then check these blogs

Do you use ISNULL(...). Don't, it does not perform- short blog by Denis Gobo
Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)- long and comprehensive article by Erland Sommarskog
Catch All Queries- short blog by Gail Shaw
Sunday T-SQL tip: How to select data with unknown parameter set Nice blog by Dmitri Korotkevitch


PluralSight Learning Library

 

[RTag]

Just combine possible @CustomerID values like this:
SELECT ....
FROM Customers
WHERE @CustomerID IS NULL OR CustomerID = @CustomerID

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.

 

[gmmastros]

RTag,

There is a performance penalty with your approach/suggestion. There is likely an index on the CustomerId column. So, when a value is passed in for @CustomerId, SQL Server should use an index seek to retrieve the row(s) it is looking for. When @CustomerId is set to NULL, then all data should be returned, which means that an index seek is not the best approach.

With your suggested query, SQL Server will likely do an index scan regardless of the @CustomerId value, so you will not get any performance improvement when you are looking for a single row.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RTag]

I agree there will be a penalty.
However the option to get all the records in the table (when @CustomerID is null) means that the number of the records in the table will be limited anyway (except if there is another WHERE clause not shown in the example)
Definitely not the best practice though.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send you report everywhere.