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