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

User · Answer

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  gt  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  gt  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  gt  varMinStatus OR name LIKE varType

User · Answer

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

User · Answer

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

User · Answer

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  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[tsql] SQL Views - no variables?

Examples related to tsql

Examples related to sql-view