Got a little bit of an design question for all you sql experts. I'm looking for a little more info then a simple yes or no.
My co-worker and I are working with a rather large MS SQL 2005 DB. We have a table that contains about 12 fields with the key made up of a single field. One of the fields is named IsActive, as you can imagine this field is a BIT that lets us know if the current record is considered active or inactive. When writing a stored procedures we considered various approaches.
1.) writing two stored procedures, one to query on a parameter @IsActive and one SP to return All records regardless of IsActive (i.e. IsActive = 0 or 1.)
2.) writing a single sp to get all data and filter on the other end of the pipe.
3.) writing a single sp that accepts @IsActive and defaults to NULL .. if NULL then the sp splits on an IF statement to execute a query that gets all data, If @IsActive is 0 or 1 then executes a query to match the @IsActive bit.
What we think of each solution:
Solution 1 isn't good for use because it *2 the number of sp's needed to write for the table.
Solution 2 isn't good because it sends a lot of extra data over the pipe.
Solution 3 isn't good because the DBMS can't optimize the sp properly since it contains various queries and splits on IF THEN logic.
I'm curious what you guys have done, experienced, learned in the past when dealing with a situation like this.
Thanks guys, looking forward to your input.