Search code examples
sqlsql-serversql-view

Am I forced to use a CTE or stored procedure instead of a view?


I want to reduce the size of my code and have a query that runs as fast as possible.

I have one CASE expression copy-pasted multiple times within the same SELECT. This is redundant and may run the switch multiple times instead of once, making it slower than it should (Or doesn't it? Does the SQL graph detect the redundant code and reuse results?)

-- My view.
SELECT
    /* 20 lines case expression */ + 1 AS a,
    /* 20 lines case expression */ + 2 AS b,
    /* 20 lines case expression */ + 3 AS c,
    /* 20 lines case expression */ + 4 AS d,
FROM some_table

To avoid repeating myself I would like to do the following:

-- The following code is not valid. It is meant to represent my goal.
SELECT @temp := /* 20 lines case expression */ AS complex
FROM some_table;

SELECT
    @temp + 1 AS a,
    @temp + 2 AS b,
    @temp + 3 AS c,
    @temp + 4 AS d,
FROM some_table

But as you may know, user-defined variables are not a thing with views. Also, CTEs are often described as being slow (To which extent and in which situations I do not know).

I would like to use a view alone, is it possible? (speed is more important) If not, then it's fine, I'll just do one of the following.

  • Just keep the copy/pasted code
  • Use a stored procedure for the case expression (adding to the many procedures cluttering the database)
  • Simply using a CTE (because in this case it may be fast?)

Thanks for your help and for sharing your approach to this problem.

UPDATE:

On your demands, here is a more practical example of what I'm dealing with. It's based on legacy code, please do not credit me for it.

-- It's a simplified example with small alterations.
SELECT
    -- Nothing special ...
    [col_x] AS X,
    [col_y] AS Y,
    [col_z] AS Z,
    -- Then there is this column...
    CASE
        WHEN
            [col_a] + ' ' +
            CASE
                WHEN [col_b] = 'some_code' THEN 'something'
                ELSE 'something_else'
            END + ' ' +
            CASE
                WHEN [col_c] IN ('a', 'b', 'c') THEN 'something'
                ELSE 'something_else'
            END = 'First thing to compare with'
        THEN [return_First],
        WHEN
            [col_a] + ' ' +
            CASE
                WHEN [col_b] = 'some_code' THEN 'something'
                ELSE 'something_else'
            END + ' ' +
            CASE
                WHEN [col_c] IN ('a', 'b', 'c') THEN 'something'
                ELSE 'something_else'
            END = 'Second thing to compare with'
        THEN [return_Second],
        WHEN
            [col_a] + ' ' +
            CASE
                WHEN [col_b] = 'some_code' THEN 'something'
                ELSE 'something_else'
            END + ' ' +
            CASE
                WHEN [col_c] IN ('a', 'b', 'c') THEN 'something'
                ELSE 'something_else'
            END = 'Second thing to compare with'
        THEN [return_Third]
    END AS [Monster Case Expression That Makes Me Cry]
FROM some_table
       

Solution

  • The best way to do this is to put the expression in an APPLY, which means you can re-use it anywhere in the query:

    SELECT
        v1.Temp + 1 AS a,
        v1.Temp + 2 AS b,
        v1.Temp + 3 AS c,
        v1.Temp + 4 AS d,
    FROM some_table
    CROSS APPLY (VALUES (
        CASE WHEN /* 20 lines case expression */
        END
    ) ) AS v1(Temp)