problems with case statement in where clause

[shlo]

I was wondering if someone would be able to tell me what is wrong with my code here:

SELECT COUNT(*)
FROM CALL
WHERE CALL.DESCRIPTIO N =
CASE
WHEN @Param1 = 'In' THEN 'In'
WHEN @Param1 = 'Out' THEN 'Out'
END

I am getting an error that parameter is correct.

And just in case there is any confusion, I am taking in 1 parameter that is either going to be 'In' or 'Out'. Depending on the param, I want to set Call.Descriptio n to either 'In' or 'Out'.

Thanks in advance!

[ck9663]

I was wondering if someone would be able to tell me what is wrong with my code here:

SELECT COUNT(*)
FROM CALL
WHERE CALL.DESCRIPTIO N =
CASE
WHEN @Param1 = 'In' THEN 'In'
WHEN @Param1 = 'Out' THEN 'Out'
END

I am getting an error that parameter is correct.

And just in case there is any confusion, I am taking in 1 parameter that is either going to be 'In' or 'Out'. Depending on the param, I want to set Call.Descriptio n to either 'In' or 'Out'.

Thanks in advance!

What error are you getting?

You can actually just do this:

SELECT     COUNT(*) 
FROM         CALL
WHERE     CALL.DESCRIPTION =  @Param1

-- CK

[shlo]

What error are you getting?

You can actually just do this:

SELECT     COUNT(*) 
FROM         CALL
WHERE     CALL.DESCRIPTION =  @Param1

-- CK

Sorry, I forgot my else statement in my sample code:

What I'm trying to do is set get the count of calls based on the parameter. The parameter determines whether call description is equal to 'In', 'Out', or 'Any'. If any, I want to view calls with description of 'In' or 'Out'.

So,
if @Param1 = 'In', then call.descriptio n = 'In'
if @Param1 = 'Out', then call.descriptio n = 'Out'
if @Param1 = 'Any' then call.descriptio n = 'In' OR call.descriptio n = 'Out'

This is why I was using a case statement. Any suggestions would be greatly appreciated!! And I was getting an error that 'Parameter is Incorrect'.

[ck9663]

So if I understand it right:

if @param = 'In' then you want all rows with CALL.DESCRIPTIO N = In'.
if @param = 'Out' then you want all rows with CALL.DESCRIPTIO N = Out'.
if @param = 'Any' then you want all rows, both CALL.DESCRIPTIO N = 'In' or CALL.DESCRIPTIO N = 'Out'

If my assumption is wrong, don't read the following, otherwise try the following:

SELECT COUNT(*)
FROM CALL
WHERE (CALL.DESCRIPTION =
CASE
WHEN @Param1 = 'In' THEN 'In'
WHEN @Param1 = 'Out' THEN 'Out'
END) or  (@Param1 = 'Any' and CALL.DESCRIPTION in ('In','Out'))
 

-- CK

[shlo]

So if I understand it right:

if @param = 'In' then you want all rows with CALL.DESCRIPTIO N = In'.
if @param = 'Out' then you want all rows with CALL.DESCRIPTIO N = Out'.
if @param = 'Any' then you want all rows, both CALL.DESCRIPTIO N = 'In' or CALL.DESCRIPTIO N = 'Out'

If my assumption is wrong, don't read the following, otherwise try the following:

SELECT COUNT(*)
FROM CALL
WHERE (CALL.DESCRIPTION =
CASE
WHEN @Param1 = 'In' THEN 'In'
WHEN @Param1 = 'Out' THEN 'Out'
END) or  (@Param1 = 'Any' and CALL.DESCRIPTION in ('In','Out'))
 

-- CK

That worked perfectly. Thanks so much for your help!

[shlo]

Another question about case statements, now in ORDER BY.
Here is my query:

SELECT START_DATE, CALL_ID
FROM CALL

ORDER BY
CASE WHEN @OrderBy ='A' THEN START_DATE, CALL_ID
WHEN @OrderBy ='D' THEN START_DATE DESC, CALL_ID
WHEN @OrderBy ='R' THEN CALL_ID, START_DATE DESC
END

I looked up a bunch of articles and they seem to give this syntax. But, I get a SQL Execution Error with a message: Must declare the scalar variable @OrderBy. I am not doing this in a context of a stored procedure, so what am I missing?

Thanks in advance...

[ck9663]

Anything that starts with "@" is a variable. If you mean column, remove that symbol.

-- CK

[shlo]

Anything that starts with "@" is a variable. If you mean column, remove that symbol.

-- CK

I meant it to be a parameter that dynamically determines the ORDER BY. Since this is not a stored procedure, however, I don't know how to define the parameter before run time. Does this make sense?

[ck9663]

Where are you running it? SQL Analyzer?

The query needs to know the value of those variable before it runs. You have to declare it and store a value to it before your query can run.

-- CK

[shlo]

Where are you running it? SQL Analyzer?

The query needs to know the value of those variable before it runs. You have to declare it and store a value to it before your query can run.

-- CK

I'm testing this in SQL Server 2005, Management Studio.

Correct me if I'm wrong, but when you're just running a query in SS, you can just type the query, press 'Execute' and enter the parameters at that time, no? I'm confused because when I have a query with parameters in the SELECT or WHERE clause, it works perfectly fine.

SELECT     *
FROM CALL
WHERE CALL.DESCRIPTION =  @Param1

However, when I have the parameter in the ORDER BY, I'm being asked to define the variable. The exact error message: Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@orderBy".

SELECT START_DATE, CALL_ID
FROM CALL
ORDER BY CASE WHEN @orderBy = 'A' THEN START_DATE, CALL_ID END

I guess the real question is this second bit of code correct? If so, what is causing the error message of 'Must declare the scalar variable 'OrderBy''? All the examples I've seen only show dynamic ORDER BY in stored procedures, so I don't know.

Thanks again.