SQL Views - no variables?

Question

Is it possible to declare a variable within a View? For example:

Declare @SomeVar varchar(8) = 'something'

gives me the syntax error:

Incorrect syntax near the keyword 'Declare'.

Autum

Answer 1

You are correct. Local variables are not allowed in a VIEW.

You can set a local variable in a table valued function, which returns a result set (like a view does.)

http://msdn.microsoft.com/en-us/library/ms191165.aspx

e.g.

CREATE FUNCTION dbo.udf_foo()
RETURNS @ret TABLE (col INT)
AS
BEGIN
  DECLARE @myvar INT;
  SELECT @myvar = 1;
  INSERT INTO @ret SELECT @myvar;
  RETURN;
END;
GO
SELECT * FROM dbo.udf_foo();
GO

Athirst

Is Its efficiency similar to a view's? – Bloodmobile

No, TVFs are often slower. "SQL Server’s table-valued functions (TVFs) seem like a good idea, but they mask a host of potential performance problems. TVFs cause portions of an execution plan to stay serial (they’ll avoid parallelism), they produce bad row estimations, and multi-statement TVFs may not even get the best optimization available. In short – TVFs stink." brentozar.com/blitzcache/tvf-join – Ribbonfish

Answer 2

You could use WITH to define your expressions. Then do a simple Sub-SELECT to access those definitions.

CREATE VIEW MyView
AS
  WITH MyVars (SomeVar, Var2)
  AS (
    SELECT
      'something' AS 'SomeVar',
      123 AS 'Var2'
  )

  SELECT *
  FROM MyTable
  WHERE x = (SELECT SomeVar FROM MyVars)

Winifred

this is a constants, not a variables! – Ramtil

@Ramtil It can just as easily use (filtered?) data from a table. – Crissycrist

Answer 3

EDIT: I tried using a CTE on my previous answer which was incorrect, as pointed out by @bummi. This option should work instead:

Here's one option using a CROSS APPLY, to kind of work around this problem:

SELECT st.Value, Constants.CONSTANT_ONE, Constants.CONSTANT_TWO
FROM SomeTable st
CROSS APPLY (
    SELECT 'Value1' AS CONSTANT_ONE,
           'Value2' AS CONSTANT_TWO
) Constants

Swam

Thanks for the correction - updated to use the CROSS APPLY instead. – Swam

This works, but don't the columns of the Cross Apply get reinitialized for every row? Especially for calculated values that would mean a big performance loss. It's just sad that Local Variable and CTE are not available in a View, anyone an idea why? – Sordid

@Sordid you can create and use 'CTE' in 'View'. – Guidon

Answer 4

@datenstation had the correct concept. Here is a working example that uses CTE to cache variable's names:

CREATE VIEW vwImportant_Users AS
WITH params AS (
    SELECT 
    varType='%Admin%', 
    varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers, params
    WHERE status > varMinStatus OR name LIKE varType

SELECT * FROM vwImportant_Users

also via JOIN

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers INNER JOIN params ON 1=1
    WHERE status > varMinStatus OR name LIKE varType

also via CROSS APPLY

WITH params AS ( SELECT varType='%Admin%', varMinStatus=1)
SELECT status, name 
    FROM sys.sysusers CROSS APPLY params
    WHERE status > varMinStatus OR name LIKE varType

Brython

Answer 5

Yes this is correct, you can't have variables in views (there are other restrictions too).

Views can be used for cases where the result can be replaced with a select statement.

Duodecillion

A table function can be replaced in a select statement and it has local variables. – Leukoderma

Are you saying since a select statement can't have local variables, a view can't either? – Leukoderma

@Leukoderma "you can't have variables in views" is what I said. Is this unclear? – Duodecillion

It's the last sentence. A view can replace a select statement but what does that have to do with variables? Couldn't a table function replace a select statement, but include variables? – Leukoderma

@Leukoderma -- I feel like you are trying to "catch" me on a technicality -- like this is a puzzle to prove me wrong or something. I believe my statement does not talk about functions and the question is not about functions so what is your point? Do functions have their own rules which may or may not be allowed in a view depending on the platform? Sure. But isn't that just a bit off topic? (and not at all what this question about) – Duodecillion

@Duodecillion - I'm wiling to risk splitting hairs to learn something. – Leukoderma

A table function can be replaced with a select statement as well, but table functions cannot be used everywhere that a view can, such as joins. What he is trying to say is that a view can replace a single select statement, but cannot replace multiple statements. The BEGIN keyword is invalid in a CREATE VIEW statement, as well as an inline function. It would be needed to create a multistatement script. Procedures or multiple statement functions are probably the best way to do this. – Calabria

Answer 6

Using functions as spencer7593 mentioned is a correct approach for dynamic data. For static data, a more performant approach which is consistent with SQL data design (versus the anti-pattern of writting massive procedural code in sprocs) is to create a separate table with the static values and join to it. This is extremely beneficial from a performace perspective since the SQL Engine can build effective execution plans around a JOIN, and you have the potential to add indexes as well if needed.

The disadvantage of using functions (or any inline calculated values) is the callout happens for every potential row returned, which is costly. Why? Because SQL has to first create a full dataset with the calculated values and then apply the WHERE clause to that dataset.

Nine times out of ten you should not need dynamically calculated cell values in your queries. Its much better to figure out what you will need, then design a data model that supports it, and populate that data model with semi-dynamic data (via batch jobs for instance) and use the SQL Engine to do the heavy lifting via standard SQL.

Turpentine

Answer 7

What I do is create a view that performs the same select as the table variable and link that view into the second view. So a view can select from another view. This achieves the same result

Prescience

Ben, this would likely cause a performance issue unless you are dealing with very small tables. – Cami

Even with very small table (3 records), a View with million records causes big performance issues. – Astonied

Answer 8

How often do you need to refresh the view? I have a similar case where the new data comes once a month; then I have to load it, and during the loading processes I have to create new tables. At that moment I alter my view to consider the changes. I used as base the information in this other question:

Create View Dynamically & synonyms

In there, it is proposed to do it 2 ways:

1. using synonyms.
2. Using dynamic SQL to create view (this is what helped me achieve my result).

Bloodmobile