Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 12th, 2006, 05:01 PM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default USING SQL IF Statement in WHERE clause

Hi All

I am a little bit of a novice with SQL but am a keane learner!

I have a SELECT query and I am trying to use an If stament within the WHERE clause but can't get it to behave. Here is similar to what I have so far...

Code:
Stored PROC STUFF..

  @IDCODE nvarchar(3)
  @NAME varchar(50)
AS
  SELECT * FROM tblTABLE
  WHERE ((NAME LIKE @NAME)AND
     IF @IDCODE <> 1 
           (IDCODE=@IDCODE))
     END IF
  ORDER BY NAME

GO
.. can anyone tell what I should be doing /doing wrong?

Many thanks

Rit
__________________
Rit
www.designandonline.co.uk
INSPIRE | CREATE | DELIVER
  #2 (permalink)  
Old February 13th, 2006, 09:27 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, I am not quite sure what u are trying to do, but you could try something like this:


Code:
Stored PROC STUFF..

  @IDCODE nvarchar(3)
  @NAME varchar(50)
AS
  DECLARE @IDCODE2 nvarchar(3)
  SET @IDCODE2 = NULL

  IF @IDCODE <> 1
  BEGIN
    @IDCODE2 = @IDCODE
  END

  SELECT * FROM tblTABLE
  WHERE ((NAME LIKE @NAME)AND
     IDCODE = COALESCE(@IDCODE2,IDCODE))
  ORDER BY NAME

GO
  #3 (permalink)  
Old February 13th, 2006, 06:11 PM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

Thanks Gert,

What I am trying to achieve is.. I have a search page that includes a ‘NAME’ field and a Country field called ‘IDCODE’ and depending on your user status will depend on whether you can only search within your country or be able to search all countries.

My ASPX code checks their user status and populates a dropdown box with either their country code (e.g. IDCODE=3) or the ALL code (e.g. IDCODE=1)

The IF statement within my SQL was basically looking to see if the IDCODE was 1 and if so would not include an ICODE criteria within the WHERE clause and vice-versa
  #4 (permalink)  
Old February 13th, 2006, 06:22 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi Rit,

You don't need an IF for this. You can simply compare the *parameter* with the required value.

E.g.:

SELECT SomeField FROM SomeTable
WHERE SomeField = @someParam OR @someParam = 1

So, when you pass 1 for the @someParam param, all records are returned, because 1 always equals one....

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
  #5 (permalink)  
Old February 13th, 2006, 06:48 PM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

HI Imar, Thanks for your response. But 1 in this case isn't to compare against data within a column but to widen the WHERE scope.. I'll try and explain a little better

IDCODE is a column within my DB..
If @IDCODE=1 then lose the WHERE clause on the ICODE column
OTHERWISE include the ICODE column and use the @ICODE parameter to filter it.

1 is just to identify whether we use the WHERE clause on the ICODE.

There probably a much simpler way of doing this. Ideally if I could use the wildcard that would be better but I wanted to stay clear of 'LIKE' as I am dealing with Integers.

Ta
Rit
  #6 (permalink)  
Old February 13th, 2006, 07:32 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

How about:


CREATE PROCEDURE getName
 @IDCODE nvarchar,
  @NAME varchar
AS

BEGIN

IF @IDCODE <> 1 THEN
   SELECT * FROM tblTABLE ORDER BY NAME
ELSE
   SELECT * FROM tblTABLE WHERE NAME LIKE @NAME AND IDCODE = @IDCODE ORDER BY NAME

END

My ID type columns are usually integers so that's probably one different way I would handle the @IDCODE.

@IDCODE int



  #7 (permalink)  
Old February 13th, 2006, 07:47 PM
Friend of Wrox
 
Join Date: Oct 2005
Location: , , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 1 Post
Default

Big appologies to Gert. Your theory did work using COALESCE... I just need to read up a little on this.

Many thanks all!.
  #8 (permalink)  
Old February 13th, 2006, 08:18 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

I actually like Imar's solution best. Very elegant.

Please, someone buy him some aquavit.

Oh, yeah, I almost forgot, that would be me.

  #9 (permalink)  
Old February 14th, 2006, 02:16 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I agree. You should go with Imar's solution. It is more elegant and proparbly more effective.

Gert

  #10 (permalink)  
Old February 14th, 2006, 05:13 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi Rit,

You may have figured it out by now, but my solution does exactly what you want. The 1 in the param value is used to remove the impact of the WHERE clause entirely:

Consider my original WHERE clause:
Code:
WHERE SomeField = @someParam OR @someParam = 1
Let's say @someParam equals 4. Then the WHERE clause will be:
Code:
WHERE SomeField = 4 OR 4 = 1
This returns all records that have the value 4 in the SomeField column. Only the part before the OR has effect, because 4 does not equal 1.

However, if @someParam itself (the value of the parameter) equals 1, the where clause looks like this:
Code:
WHERE SomeField = 1 OR 1 = 1
The OR 1 = 1 is important here. Since 1 always equals 1, the second part (after the OR statement) always returns true, effectively removing the WHERE restrictions all together.

In a more complex WHERE clause, you may need to add additional parentheses to block this additional option from removing the effect of the entire WHERE clause.

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Case Statement In Where Clause Logic dwj119 SQL Server 2000 4 October 27th, 2011 03:14 PM
If statement in WHERE clause frakey Classic ASP Basics 30 July 8th, 2008 03:35 PM
Problems using case statement in where clause vghiya SQL Server 2000 3 May 28th, 2007 05:12 AM
Select statement with where clause with 2 conditon Yasho VB.NET 2002/2003 Basics 7 May 16th, 2007 01:40 PM
If statement in Where Clause jdziggy SQL Server 2000 2 March 16th, 2007 08:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.