August 4, 2011 at 11:16 am
Hi All,
I have a procedure that will get all the relevant information from a table depending on what values have been passed in,
Some of the values might come in as NULL.
If it has a Null Value then i wont include it in the Where clause.
If it does have a value then i will include it in the Where Clause.
Example
Select * from TableName
Where Firstname = @Firstname and Surname = @Surname and EyeColor = @EyeColor
So how can i check to see if there are null values in the parameters, again the values of the parameters will change due to different users using the software, the above is an example its not the actually procedure.
Below is an example of what im trying to achieve (pretty bad example i know)
where Userid = @user-id if @Firstname <> Null then firstname = @Firstname
But if it is null then........This is where im stuck. :(
Can someone shed some light on this please.
Thanks in Advance
August 4, 2011 at 11:22 am
First, you can't use "IF" in a Where clause, or, for that matter, anywhere inside of a query. It's a flow-control item, not a query-logic item.
Gail Shaw (Gila Monster) has an excellent blog post on the subject of your actual query in her blog, here: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2011 at 11:46 am
Ok thanks for the information, ill take a look at that
Just another question
Ok taking in to consideration that you cant use the IF statement within Where Clauses is there a way of me checking before the where clause
So instead of me removing the parameters can i say if one of the parameters is null then Fill it with a Default value like '%%' or something? so it does check before the where statement etc etc
August 4, 2011 at 12:19 pm
create proc YourProc
@arg1 varchar(25) = null
as
set nocount on
select * from mytable
where (@arg1 is null or mytable.mycolumn = @arg1)
August 4, 2011 at 12:29 pm
That does work logically, but it's horrible to index for, especially in more complex queries.
IF @arg1 IS NULL
select * from mytable
ELSE
select * from mytable
where mytable.mycolumn = @arg1
may not be as exciting for a programmer 😉 , but it's a lot easier to optimise.
August 4, 2011 at 12:38 pm
.Netter (8/4/2011)
Ok thanks for the information, ill take a look at thatJust another question
Ok taking in to consideration that you cant use the IF statement within Where Clauses is there a way of me checking before the where clause
So instead of me removing the parameters can i say if one of the parameters is null then Fill it with a Default value like '%%' or something? so it does check before the where statement etc etc
Take a look at Gail's blog entry that I linked to earlier.
It has several solutions to this, and the pros and cons of each.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2011 at 12:40 pm
If you just want to check for null, you can use the following:
Select * from TableNameWhere Firstname = ISNULL(@Firstname, FirstName)
and Surname = IsNull(@Surname, SurName) and EyeColor = ISNULL(@EyeColor, EyeColor)
Hope this helps. If you have a complicated criterion, you can use CASE in WHERE instead of IF
for Example:
Select * from Table1
Where
1 = Case
When Col1 <= 100 Then 1
when Col1 between 100 and 200 then 1
else
0
End
August 4, 2011 at 12:47 pm
Philip Yale-193937 (8/4/2011)
IF @arg1 IS NULLselect * from mytable
ELSE
select * from mytable
where mytable.mycolumn = @arg1
may not be as exciting for a programmer 😉 , but it's a lot easier to optimise.
Well, easier to optimise, but prone to really erratic performance due to parameter sniffing.
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
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
August 4, 2011 at 12:49 pm
Beulah Kingsly (8/4/2011)
If you just want to check for null, you can use the following:Select * from TableNameWhere Firstname = ISNULL(@Firstname, FirstName)
and Surname = IsNull(@Surname, SurName) and EyeColor = ISNULL(@EyeColor, EyeColor)
Hope this helps. If you have a complicated criterion, you can use CASE in WHERE instead of IF
for Example:
Select * from Table1
Where
1 = Case
When Col1 <= 100 Then 1
when Col1 between 100 and 200 then 1
else
0
End
Either work, providing that performance is not a concern. Both cannot use indexes. They will do scans.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply