SQL Views - no variables?

tsql sql-view
PositiveGuy picture PositiveGuy · May 24, 2011 · Viewed 182.1k times · Source

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

Answer

spencer7593 picture spencer7593 · May 24, 2011

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

Related questions

Create view with primary key?

I create a view with following codes SELECT CONVERT(NVARCHAR, YEAR(okuma_tarihi)) + 'T1' AS sno, YEAR(okuma_tarihi) AS Yillar, SUM(toplam_kullanim_T1) AS TotalUsageValue, 'T1' AS UsageType FROM TblSayacOkumalari GROUP BY CONVERT(NVARCHAR, YEAR(okuma_…

Read More
Select Columns of a View

I'm attempting to select the column names of a view in a similar way as selecting from information_schema.columns. I can't seem to find a way to do this. Has anyone else done this before or know if it …

Read More
Create View - Declare a variable

I am creating a view that is using that STUFF function. I want to put the result of STUFF in a variable for my view. The problem I am having is declaring my variable. It gives me the message "Incorrect …

Read More