SQL unable to alter view having DECLARE statement

Question

I'm trying to alter the view below:

ALTER VIEW [dbo].[Win.v_TodayMin365]
AS

    declare @tblOut table(Dates Date)
    declare @cnt INT = -365
    while @cnt < 0
    Begin
            set @cnt = @cnt + 1;
            insert into @tblOut (Dates) values(cast(dateadd(day,@cnt,getdate()) as Date))
    END
    select * from @tblOut
    deallocate @tblOut
    deallocate @cnt
GO

The code as such works (if I highlight all between AS and GO and hit Execute, I get the expected output), but I can't run it as ALTER VIEW. Then, I get the following error:

Incorrect syntax near the keyword 'declare'. Expecting '(', SELECT or WITH

Thanks in advance for any idea!

you need to create a stored procedure.. not a view.. or maybe a table value function — JamieD77, Oct 12 '16 at 15:26
Foundation thuis post http://stackoverflow.com/questions/6114826/sql-views-no-variables — Alex Bedeker, Oct 12 '16 at 15:32
Just a touch more explanation - views are just canned queries. You can't alter data with a view. You can only retrieve. — squillman, Oct 12 '16 at 15:33

Pரதீப் · Accepted Answer · 2016-10-12T15:41:40.643

You cannot Declare a variable inside a View

Actually you don't need to use While loop for this. Use a tally table trick to generate dates much efficient than the while loop approach

ALTER VIEW [dbo].[Win.v_TodayMin365]
AS
  WITH E1(N)
       AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1), --10E+1 or 10 rows
       E2(N)
       AS (SELECT 1 FROM E1 a,E1 b), --10E+2 or 100 rows
       E4(N)
       AS (SELECT 1 FROM E2 a,E2 b), --10E+4 or 10,000 rows max
       calendar
       AS (SELECT Dateadd(dd, Row_number()OVER(ORDER BY n), Dateadd(yy, -1, Cast(Getdate() + 1 AS DATE))) AS dates
           FROM   E4 l)
  SELECT *
  FROM   calendar
  WHERE  dates <= Cast(Getdate() AS DATE)

This even can be converted to Table valued function or a Stored Procedure.

SQL unable to alter view having DECLARE statement

1 Answers1