SQL query - optional WHERE clause-CodePudding

I have the following SQL query:

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

The WHERE condition is required if level >= 1. Otherwise, the WHERE clause is not required.

Meaning,

if level >= 1, use this:

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

If level < 1, use this:

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

How do I do that?

CodePudding user response：

The simplest would be

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

But depending on indexing, performance may not be optimal. The next alternative would be to inject either the WHERE clause or an empty string into the SQL statement.

CodePudding user response：

Simplest solutions would be either creating the SQL string conditionally or setting the level to 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);

Or:

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