SQL Server：带有变量的子句

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.

我正在为一个学术机构开发一个存储过程，允许顾问按照许多标准过滤他们的学生列表。最近，顾问让我给他们选择通过GPA过滤学生。在他们要求确定不平等的选项之前，这非常容易。

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

这是我正在使用的查询的截断副本，以及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

当我尝试执行此查询时，SSMS给出了以下错误消息：'>'附近的语法不正确，引用HAVING子句中CASE语句的第一行，> = @GPA

I've tried rewriting the HAVING clause to:

我已经尝试将HAVING子句重写为：

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

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

这会产生另一条错误消息：关键字“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?

我知道我可以复制并粘贴整个SQL查询四次并且只更改每个查询中HAVING子句中的不等式，但这很麻烦（不是这不是 - 它对我来说没有味道不好）和如果可以，我想避免它。有什么方法可以让我完成上面的尝试吗？

4 个解决方案

#1

;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) = '<')

#2

You should probably try with -

你应该尝试 -

CASE ''' + @Inequality + '''

#3

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

尝试将SUM（GradePointValue * Credits）/ SUM（Credits）转换为DECIMAL。

#4

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

您可以使用CASE语句模仿多个WHERE子句。

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

#1

;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) = '<')

#2