CODEWITHSUNDEEP
sql-serversql-server-2005
jwheron
jwheron

Reputation: 2562

SQL Server: Having clause with variables

I'm working on a stored procedure for an academic institution that would allow advisors to filter their student lists by a number of criteria. Recently, the advisors asked me to give them the option of filtering students by GPA. This was perfectly easy until they asked for an option to determine the inequality.

Here's a truncated copy of the query I'm using, and the corresponding error message in SQL.

DECLARE @Inequality CHAR(2)
SET @Inequality = '>='

DECLARE @Gpa DECIMAL(3,1)
SET @Gpa = 2.0

SELECT DISTINCT
    mem.StudentID,
    SUM(GradePointValue * Credits) / SUM(Credits) AS GPA
FROM <snip>
WHERE <snip>
GROUP BY mem.StudentID
HAVING 
    CASE @Inequality
        WHEN '>=' THEN SUM(GradePointValue * Credits) / SUM(Credits) >= @Gpa
        WHEN '>'  THEN SUM(GradePointValue * Credits) / SUM(Credits) >  @Gpa
        WHEN '<'  THEN SUM(GradePointValue * Credits) / SUM(Credits) <  @Gpa
        WHEN '<=' THEN SUM(GradePointValue * Credits) / SUM(Credits) <= @Gpa
    END 
ORDER BY GPA desc

When I try to execute this query, SSMS gives me the following error message: Incorrect syntax near '>'., referencing the first line of the CASE statement in the HAVING clause, >= @GPA

I've tried rewriting the HAVING clause to:

HAVING SUM(GradePointValue * Credits) / SUM(Credits)
    CASE @Inequality
        WHEN '>=' THEN >=
        ...
    END @Gpa

This produces another error message: Incorrect syntax near the keyword 'CASE'.

I know I could copy and paste the entire SQL query four times and change only the inequality in the HAVING clause in each query, but that's messy (not that this isn't -- it just doesn't smell as bad to me) and I'd like to avoid it if I can. Is there any way for me to pull off my attempt above?

Upvotes: 0

Views: 4227

Answers (4)

tponthieux
tponthieux

Reputation: 1552

You can mimic having multiple WHERE clauses with a CASE statement.

DECLARE
 @Inequality VARCHAR(2)
,@Gpa FLOAT
SET @Inequality = '>='
SET @Gpa = 2.0


SELECT
 V.StudentID
,V.GPA

FROM (
    SELECT
     mem.StudentID
    ,SUM(GradePointValue * Credits)/SUM(Credits) AS GpaValue

    FROM <snip>

    WHERE <snip>

    GROUP BY mem.StudentID
) AS V

WHERE CASE WHEN @Inequality = '>=' AND V.GpaValue >= @Gpa THEN 1
        WHEN @Inequality = '>' AND V.GpaValue > @Gpa THEN 1
        WHEN @Inequality = '<' AND V.GpaValue < @Gpa THEN 1
        WHEN @Inequality = '<=' AND V.GpaValue <= @Gpa THEN 1
        ELSE 0 END = 1

You could also do this if you don't want an inline view.

SELECT
 mem.StudentID
,SUM(GradePointValue * Credits)/SUM(Credits) AS GpaValue

FROM <snip>

WHERE <snip>

GROUP BY mem.StudentID

HAVING CASE WHEN @Inequality = '>=' AND SUM(GradePointValue * Credits)/SUM(Credits) >= @Gpa THEN 1
        WHEN @Inequality = '>' AND SUM(GradePointValue * Credits)/SUM(Credits) > @Gpa THEN 1
        WHEN @Inequality = '<' AND SUM(GradePointValue * Credits)/SUM(Credits) < @Gpa THEN 1
        WHEN @Inequality = '<=' AND SUM(GradePointValue * Credits)/SUM(Credits) <= @Gpa THEN 1
        ELSE 0 END = 1

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453338

;WITH cte As
(
SELECT /*Don't need DISTINCT with GROUP BY*/
    mem.StudentID,
    SUM(GradePointValue * Credits) / SUM(Credits) AS GPA
FROM <snip>
WHERE <snip>
GROUP BY mem.StudentID
)
SELECT StudentID, GPA
FROM cte WHERE (GPA > @Gpa AND LEFT(@Inequality, 1) = '>')
            OR (GPA = @Gpa AND RIGHT(@Inequality,1) = '=')
            OR (GPA < @Gpa AND LEFT(@Inequality,1) = '<')

Upvotes: 4

Tony Casale
Tony Casale

Reputation: 1537

Try casting your SUM(GradePointValue * Credits) / SUM(Credits) to a DECIMAL.

Upvotes: 0

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

You should probably try with -

CASE ''' + @Inequality + '''

Upvotes: 1

Related Questions