WHERE clause question

[rds80]

Platform is a parameter being passed into the stored procedure.

For now it could have 3 different values. If it equals the 3 different values the selected result would depend on that where clause. But if it doesn't meet the 3 different values (for example NULL) then I want all of the data (the data for all 3 values).

Can I write a case statement in a WHERE clause?

Any other day I would be able to figure this out.

 

[rac2]

Use Query Analyzer and give it a try.

Note that all of the possible values calculated in a CASE expression must be of the same type.

Also note that a CASE expression is a calculation, a function, not a flow control statement like IF ... ELSE .


But another approach is to use an indicator variable or value like this

/* Indicator Value*/
SELECT * FROM MyTable
WHERE ( @x = 'All values' OR column_x = @x )
  AND 
      ( @y = 99 OR column_y = @y )

/* Indicator Variable*/
SELECT * FROM MyTable
WHERE ( @AllX = 1 OR column_x = @x )
  AND 
      ( @AllY = 1 OR column_y = @y )

 

[rds80]

Not sure if that would work in the WHERE clause

I would like to try something like this:

CREATE PROCEDURE sp_Platform
(
   @PlatformID nvarchar(20)
)

AS

If @PlatformID IS NULL
BEGIN
  SET @PlatformID = ('12', '13', '20')
END

SELECT *
FROM TestTable
WHERE Platform IN @PlatformID

So that way I could do EXEC sp_platform 12 or EXEC sp_platform and get all the results back.

Thanks.

 

[rac2]

Well you may find that SQL Server will give you an error message for that code.

If you wish to retrieve all platforms when @PlatformID is null then you could

SELECT *
FROM TestTable
WHERE Platform = @PlatformID
   OR @PlatformID IS NULL

OR if you wish to retrieve a specific list of platforms only then

SELECT *
FROM TestTable
WHERE Platform = @PlatformID
   OR ( @PlatformID IS NULL AND @PlatformID IN ('12', '13', '20' )

 

[bborissov]

That is useless:
(@PlatformID IS NULL AND @PlatformID IN ('12', '13', '20' ))

@PlatformID can't be NULL and at the same time IN ('12', '13', '20' )

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rds80]

my co-worker helped me with this..
I set the default value of @PlatformID = -1

Then the where clause is:
WHERE ((@PlatformID = -1) OR ((@PlatformID <> -1 AND Platform = @PlatformID)))

The only question I have now is that if I do EXEC sp_platform, does that pass a value of NULL to sp_platform and bypass the WHERE clause as NULL doesn't = -1 or the 2nd part of the WHERE clause.

Thanks.

 

[bborissov]

That is enough:

WHERE (@PlatformID = -1 OR Platform = @PlatformID)

If you didn't pass a parameter to your SP the parameter is initialised as NULL if you didn't have a default value for it:

CREATE PROCEDURE sp_Platform
(
   @PlatformID int = -1
)


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rac2]

Useless is a harsh word Boris.

Maybe I meant to type IS NOT NULL. Maybe I was tired or distracted.

 

[bborissov]

Sorry rac2, I didn't meant to offense you. Maybe I didn't got the right word for this. Maybe "redundant" is the word?
Really, Really sorry.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rac2]

Thank you for that. All is forgiven.