SQL查询-可选的WHERE子句

Question

我有以下SQL查询：

SqlCommand cmd = new SqlCommand(@$"
WITH emp AS (
SELECT *, 1 AS Level
FROM {tableName}
WHERE PersonnelNumber = {personnelNumber}

UNION ALL

SELECT e.*, emp.Level + 1
FROM {tableName} e INNER JOIN emp
ON e.ReportsToPersonnelNbr = emp.PersonnelNumber
)
SELECT e.*
FROM emp e
WHERE e.Level <= {level}; -----------------------> OPTIONAL LINE
", conn);

如果级别>=，则需要WHERE条件1.否则，不需要WHERE子句。

含义，

如果级别>=1、使用这个：

SqlCommand cmd = new SqlCommand(@$"
WITH emp AS (
SELECT *, 1 AS Level
FROM {tableName}
WHERE PersonnelNumber = {personnelNumber}
UNION ALL
SELECT e.*, emp.Level + 1
FROM {tableName} e INNER JOIN emp
ON e.ReportsToPersonnelNbr = emp.PersonnelNumber
)
SELECT e.*
FROM emp e
WHERE e.Level <= {level}
", conn);

如果级别<1、使用这个：

SqlCommand cmd = new SqlCommand(@$"
WITH emp AS (
SELECT *, 1 AS Level
FROM {tableName}
WHERE PersonnelNumber = {personnelNumber}
UNION ALL
SELECT e.*, emp.Level + 1
FROM {tableName} e INNER JOIN emp
ON e.ReportsToPersonnelNbr = emp.PersonnelNumber
)
SELECT e.*
FROM emp e;                    
", conn);

我该怎么做？

Answer 1

最简单的是

WHERE (e.Level <= {level}) OR ({level} < 1)

但根据索引的不同，性能可能不是最佳的。下一种选择是将WHERE子句或空字符串注入SQL语句。

Answer 2

最简单的解决方案是有条件地创建SQL字符串或将级别设置为MaxValue。

SqlCommand cmd = new SqlCommand(@"
WITH emp AS (
SELECT *, 1 AS Level
FROM {tableName}
WHERE PersonnelNumber = {personnelNumber}
UNION ALL
SELECT e.*, emp.Level + 1
FROM {tableName} e INNER JOIN emp
ON e.ReportsToPersonnelNbr = emp.PersonnelNumber
)
SELECT e.*
FROM emp e" +
level < 1 ? "" : $" WHERE e.Level <= {level}"
, conn);

或者：

level = level < 1 ? int.Maxvalue : level;

Answer 3

不同的查询

我在字符串级别处理这种类型的问题：

const string X_QUERY_START = @"
WITH emp AS (
...
SELECT e.*
FROM emp e";
const string X_QUERY_END = @";"
...
var queryX = X_QUERY_START
+ level >= 1 ? "WHERE e.Level <= {level}" : null
+ X_QUERY_END;

你可以让你的字符串构建更好，但这表明了

[破解]将等级设置为666

如果级别<1、保持WHERE e.Level <= {level}，但要做大；它足够大，可以抓住所有价值观。