k3opie
asked on
Multiple SQL statements in a single stored procedure
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.
Andrew
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.
Andrew
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE Procedure MyProc
@isActive bit = NULL
AS
BEGIN
SELECT * FROM YourTable WHERE (IsActive = @isActive or @isActive IS NULL)
END
@isActive bit = NULL
AS
BEGIN
SELECT * FROM YourTable WHERE (IsActive = @isActive or @isActive IS NULL)
END
Hi,
Option 4 - write a single SP that accepts @IsActive and defaults to NULL.
Then write a single query, with a WHERE clause including:
WHERE IsActive = CASE WHEN @IsActive IS NULL THEN IsActive ELSE @IsActive END
Option 4 - write a single SP that accepts @IsActive and defaults to NULL.
Then write a single query, with a WHERE clause including:
WHERE IsActive = CASE WHEN @IsActive IS NULL THEN IsActive ELSE @IsActive END
ASKER
This is a very elegant solution and my gut tells me that "WHERE (IsActive = @isActive or @isActive IS NULL)" would select IsActive = 0 or 1 and IsActive = NULL in other cases. But, can't always follow your gut!
This solution is exactly what i'm looking for .
I'm interested does anyone know if writing splits on a if then statement does actually degrade the sp efficients a noticable amount?
This solution is exactly what i'm looking for .
I'm interested does anyone know if writing splits on a if then statement does actually degrade the sp efficients a noticable amount?
ASKER
can someone please provide a little more insight to the whole logical splits in a sql sp and it's effect on performance of the execution plan?
better you try the two solutions and check the execution plans
ASKER
aneeshattingal, i was maybe looking for a little more insight on what people have seen, used, done in the past but the intial solution you provided was right on the money. I'm assuming this type of thing crops up pretty often for regular sql guys
We can't predict the behaviour of the sql Optimizer, according to my knowlege the WHERE(...) will run faster compared to the other
CREATE PROCEDURE YourProc(@IsActive as int = 0) AS
IF @IsActive = 0
-- Use 0 to be your 'Return All Records' switch
SELECT * FROM Foo
else
SELECT * FROM Foo WHERE IsActive=@IsActive
GO