USING SQL IF Statement in WHERE clause

rit01 · February 12th, 2006, 05:01 PM

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

Gert · February 13th, 2006, 09:27 AM

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

rit01 · February 13th, 2006, 06:11 PM

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

Imar · February 13th, 2006, 06:22 PM

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.

rit01 · February 13th, 2006, 06:48 PM

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

rstelma · February 13th, 2006, 07:32 PM

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

rit01 · February 13th, 2006, 07:47 PM

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

Many thanks all!.

rstelma · February 13th, 2006, 08:18 PM

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

Please, someone buy him some aquavit.

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

Gert · February 14th, 2006, 02:16 AM

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

Gert

Imar · February 14th, 2006, 05:13 AM

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.