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

spencer7593 · Accepted Answer · 2011-05-24T18:16:48.350

67

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

edited May 24 '11 at 18:16

answered May 24 '11 at 18:08

spencer7593

106,611
15
112
140

Is Its efficiency similar to a view's? – RaRdEvA Sep 23 '19 at 14:49
1

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." https://www.brentozar.com/blitzcache/tvf-join/ – wp78de Oct 31 '19 at 18:01

score 51 · Answer 2 · answered Dec 09 '16 at 13:49

51

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)

answered Dec 09 '16 at 13:49

datenstation

629
5
9

3

this is a constants, not a variables! – Vladislav Sep 04 '18 at 15:28
2

@Vladislav It can just as easily use (filtered?) data from a table. – Dodecaphone Sep 10 '19 at 11:29

Daniel Neel · Answer 3 · 2014-11-19T19:56:00.243

18

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

edited Nov 19 '14 at 19:56

answered Nov 17 '14 at 23:37

Daniel Neel

1,197
13
28

Thanks for the correction - updated to use the CROSS APPLY instead. – Daniel Neel Nov 19 '14 at 19:57
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? – T_D Nov 30 '15 at 15:05
1

@T_D you can create and use 'CTE' in 'View'. – Semuserable Apr 03 '19 at 10:28

Oleg Melnikov · Answer 4 · 2017-12-12T20:51:26.437

@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

score 4 · Answer 5 · answered Sep 01 '16 at 14:07

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.

score 4 · Answer 6 · answered May 24 '11 at 18:05

4

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.

answered May 24 '11 at 18:05

Hogan

69,564
10
76
117

A table function can be replaced in a select statement and it has local variables. – JeffO Mar 29 '17 at 12:28
Are you saying since a select statement can't have local variables, a view can't either? – JeffO Mar 29 '17 at 22:50
1

@JeffO "you can't have variables in views" is what I said. Is this unclear? – Hogan Mar 30 '17 at 18:06
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? – JeffO Apr 06 '17 at 16:43
@JeffO -- 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) – Hogan Apr 06 '17 at 17:14
@Hogan - I'm wiling to risk splitting hairs to learn something. – JeffO Apr 13 '17 at 16:44
1

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. – Arlen Beiler Oct 13 '17 at 18:45

score 1 · Answer 7 · answered Jun 08 '16 at 01:55

1

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

answered Jun 08 '16 at 01:55

Ben Messenger

29
6

2

Ben, this would likely cause a performance issue unless you are dealing with very small tables. – logixologist Jul 11 '16 at 20:42
Even with very small table (3 records), a View with million records causes big performance issues. – st_stefanov Jun 27 '19 at 10:39

score 0 · Answer 8 · answered Sep 23 '19 at 14:48

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:

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

SQL Views - no variables?

8 Answers8

Linked