troubleshooting Question

Multiple SQL statements in a single stored procedure

Avatar of k3opie
k3opie asked on
Microsoft SQL Server
9 Comments1 Solution290 ViewsLast Modified:
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.  
Here goes.

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.

Database Consultant
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros