March 10, 2013 at 5:08 pm
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.
March 11, 2013 at 3:56 am
I believe this article by Gail Shaw will provide you the answers you seek:
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 11, 2013 at 4:38 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2013 at 12:50 pm
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
:rolleyes:
March 12, 2013 at 1:00 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply