SQL Server Performance

Reusing lines of code

Discussion in 'Getting Started' started by lcerni, Jun 21, 2010.

  1. lcerni New Member

    I have a very large select statement. I was hoping to somehow take out the repeated code that is
    used and store it somewhere and access it through something like an include statement that is
    used in other lanuages.
    For example, I have a select statement that creates a column and then that code is expanded upon and
    stored as another column.
    SELECT (code here) AS column1,
    (code here)*5 AS column2,
    (code here)/345 AS column3
    FROM table

    I have the same code written multiple times. Is there a way to store it somewhere else and include it
    in the statement instead of writing it multiple times?
    (code here) is usually a case statement with multiple whens.
  2. Adriaan New Member

    You could create it as a view, which will make it available for other procedures too.
    If this is for use within one procedure, and for one query only, you could use the WITH syntax (SQL 2005 and up) or a derived table (a query statement between brackets, with an alias, in your FROM clause).
    If your procedure needs to access this same data more than once, then you could store the data in a table variable, or in a temporary table that you create within the procedure.
  3. lcerni New Member

    So this is an example of my code that I reuse over in the select statement. I do not believe that I can create a view with it.
    CONVERT(DECIMAL(10,1),
    (
    CASE WHEN DATEDIFF(DAY, AMD, AFD)>=0 THEN NULL
    ELSE
    (
    CAST(
    (
    (CASE
    WHEN FBI='1' THEN (CASE
    WHEN DATEDIFF(DAY, AED, AFD)>=0 THEN 0
    ELSE ABS(ROUND((DATEDIFF(DAY, AED, AFD)/365.25),10))
    END
    )
    WHEN FBI='2' AND (CASE
    WHEN ATS='A' THEN 'TRUE'
    WHEN ATS='B' THEN 'TRUE'
    WHEN ATS='C' THEN 'FALSE'
    ELSE NULL
    END)='TRUE' THEN (CASE
    WHEN DATEDIFF(DAY, AMD, AFD)>=0 THEN NULL
    ELSE CAST((ABS(ROUND((DATEDIFF(DAY, AMD, AFD)/365.25),10))) AS VARCHAR(50))
    END
    )
    WHEN FBI='3' THEN (1IT)
    WHEN FBI='4' THEN (2IT)
    --
    END)
    +
    (CASE
    WHEN TTMITB='1' THEN (CASE
    WHEN 1IT >= 2IT THEN 1IT
    WHEN 2IT >= 1IT THEN 2IT
    END
    )
    ELSE 0
    END)
    ) AS VARCHAR(20))
    )
    END
    )
    )
  4. Adriaan New Member

    The whole thing that starts with CONVERT, all the way down to the final closing brackets, is a single expression.
    In a query, instead of a column name, you can specify an expression.
    A view is always a SELECT query. So in your view, you can specify the whole expression with an alias. This alias will be the column name when you access the view.
  5. lcerni New Member

    I learned that using a common table expression would help in my case.
  6. satya Moderator

    How frequently this query will be executed?
    As it is possible to use CTE in a view, CTEs can be useful when queries need to select from a set of data that does not exist as a table within the database. As suggested Views are generally used to break down large queries so they can be queried against in a much more readable manner.
    On the general usage of CTE if you want to use a CTE, you must write the query that refers to the CTE immediately after the CTE in the T-SQL batch

Share This Page