SQL Query - AND / OR / IF / ELSE
-
Hey all, I'm writing a basic parametrized query to search a database and am attempting to allow a user to specify 1 or 2 parameters for a search. However, I am having trouble when the user specifies both parameters as the return of the query then displays 0 rows. Here is what I have: WHERE ((suburb LIKE '%' + @suburb1 + '%' AND @suburb2 ='') OR (suburb LIKE '%' + @suburb1 + '%' AND suburb LIKE '%' + @suburb2 + '%')) @suburb1 and @suburb2 are declared within that statement as parameters for the query (I am using VS2008 to construct it). It looks like I need an IF statement or similar to make it do the first part if @suburb2 = '', however I can not for the life of me work out how to do this... I am relatively new to writing queries, so I understand that I may be way off with how I'm approaching this, but some guidance in the right direction would be greatly appreciated! Joe
-
Hey all, I'm writing a basic parametrized query to search a database and am attempting to allow a user to specify 1 or 2 parameters for a search. However, I am having trouble when the user specifies both parameters as the return of the query then displays 0 rows. Here is what I have: WHERE ((suburb LIKE '%' + @suburb1 + '%' AND @suburb2 ='') OR (suburb LIKE '%' + @suburb1 + '%' AND suburb LIKE '%' + @suburb2 + '%')) @suburb1 and @suburb2 are declared within that statement as parameters for the query (I am using VS2008 to construct it). It looks like I need an IF statement or similar to make it do the first part if @suburb2 = '', however I can not for the life of me work out how to do this... I am relatively new to writing queries, so I understand that I may be way off with how I'm approaching this, but some guidance in the right direction would be greatly appreciated! Joe
Do some research into parameterised queries[^], this will allow you much greater flexibility and control and reduce the risk of sql injection attacks.
Never underestimate the power of human stupidity RAH
-
Hey all, I'm writing a basic parametrized query to search a database and am attempting to allow a user to specify 1 or 2 parameters for a search. However, I am having trouble when the user specifies both parameters as the return of the query then displays 0 rows. Here is what I have: WHERE ((suburb LIKE '%' + @suburb1 + '%' AND @suburb2 ='') OR (suburb LIKE '%' + @suburb1 + '%' AND suburb LIKE '%' + @suburb2 + '%')) @suburb1 and @suburb2 are declared within that statement as parameters for the query (I am using VS2008 to construct it). It looks like I need an IF statement or similar to make it do the first part if @suburb2 = '', however I can not for the life of me work out how to do this... I am relatively new to writing queries, so I understand that I may be way off with how I'm approaching this, but some guidance in the right direction would be greatly appreciated! Joe
Just look at:
suburb LIKE '%' + @suburb1 + '%' AND suburb LIKE '%' + @suburb2 + '%'
If
@suburb2is different from@suburb1, that will select exactly 0 rows because suburb is either like@suburb1or like@suburb2but surely not like both at the same time. UseORinstead ofANDhere. To differentiate between@suburb2being set or not, use aCASEstatement.