SQL views - no variables?

Question

Is it possible to declare a variable in a view? For example:

Declare @SomeVar varchar(8) = 'something' 

gives a syntax error:

Incorrect syntax next to the declare keyword.

Answer 1

You're right. Local variables are not allowed in VIEW.

You can set a local variable in a table-valued function that returns a set of results (for example, it pretends.)

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

eg.

 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 

Answer 2

You can use WITH to define your expressions. Then do a simple Sub-SELECT to access these 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) 

Answer 3

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

Here is one use case for CROSS APPLY to solve 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 

Answer 4

Using features like the mentioned spencer7593 is the right approach for dynamic data. For static data, a more efficient approach compatible with the design of SQL data (compared to the anti-template of massive procedural code in sprocs) is to create a separate table with static values ​​and join it. This is extremely beneficial from a perspective perspective, as SQL Engine can create efficient execution plans around JOINs, and you also have the option of adding indexes if necessary.

The disadvantage of using functions (or any built-in calculated values) is that shutdown occurs for each returned potential row, which is expensive. What for? Because SQL must first create a complete dataset with calculated values, and then apply the WHERE clause to that dataset.

Nine times out of ten you do not need dynamically calculated cell values ​​in your queries. It is much better to understand what you need, and then create a data model that supports it, and fill this data model with semi-dynamic data (for example, using batch jobs) and use the SQL Engine to do the heavy work using standard SQL.

Answer 5

Yes, that’s right, you cannot have variables in the views (there are other limitations).

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

Answer 6

@datenstation had the right concept. Here is a working example that CTE uses to cache variable 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 through 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 through 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 

Answer 7

I create a view that makes the same selection as a table variable, and a link that views in the second view. Thus, a view may choose from another view. It gives the same result.

Answer 8

How often do you need to update the view? I have a similar case when new data appears once a month; then I have to download it, and during the download process I have to create new tables. At this moment I am changing my mind to consider the changes. I used the information in this other question as a base:

Create a dynamic and synonyms view

It suggests doing this in two ways:

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