Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filtering by StatusId Expand / Collapse
Author
Message
Posted Sunday, March 10, 2013 5:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 10:26 PM
Points: 24, Visits: 183
I have a Stored Procedure which returns data based on the StatusId.

DECLARE @StatusId INT=2

SELECT * FROM Employees
WHERE StatusId=ISNULL(@StatusId,StatusId)

If the StatusId is null,then it returns all the rows from the table Employees.
And if StatusId is passed,the query returns data based on the StatusId.

When @StatusId is passed as NULL,does the above query work as self-join?

Also,comparing the above query with the one mentioned below,which query is more appropriate(best practice).

When,I compared the query plan,both output are same.

SELECT * FROM Employees e
WHERE
(
CASE
WHEN @StatusId IS NULL THEN 1
WHEN @StatusId=e.StatusId THEN 1
ELSE 0
END
)=1

Thanks.
Post #1429031
Posted Monday, March 11, 2013 3:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:53 PM
Points: 3,438, Visits: 5,390
I believe this article by Gail Shaw will provide you the answers you seek:
Catch-all Queries



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1429130
Posted Monday, March 11, 2013 4:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
jerry209 (3/10/2013)
Also,comparing the above query with the one mentioned below,which query is more appropriate(best practice).


Both will perform pretty horrid on larger tables. You're looking at table scans to resolve either.

Is that behaviour really necessary? Can you instead have one stored proc for 'fetch all' and one for 'fetch filtered by status'? If you can, that's the better approach.

You wouldn't (I hope) write methods in C# that can do radically different things depending on the parameters so don't do it with SQL stored procedures.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1429152
Posted Tuesday, March 12, 2013 12:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:11 AM
Points: 47, Visits: 134
So... you always want null results, but if @StatusId is null, return all rows? If so, I wonder how performance here would compare:

DECLARE @maxStatus int, @minStatus int
SELECT @maxStatus=9999999, @minStatus=-9999999

IF @StatusId IS NOT NULL SELECT @maxStatus=@StatusId, @minStatus=@StatusId

SELECT * FROM Employees
WHERE StatusId BETWEEN @minStatus AND @maxStatus
UNION
SELECT * FROM Employees
WHERE StatusId IS NULL


Post #1430059
Posted Tuesday, March 12, 2013 1:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:11 AM
Points: 47, Visits: 134
No, you are not necessarily returning all instances where StatusId is null, just remove the last three lines:

...UNION
SELECT * FROM Employees
WHERE StatusId IS NULL
Post #1430066
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse