2

Just wondering as I keep getting the error below but the rest of the script works.

CREATE VIEW View_I_Want AS 

DECLARE @MinDate AS DATE

SET @MinDate = 
    (SELECT MIN(Full_Date) AS First_Date
    FROM tbl.Calendar
    WHERE [Fin_Year] = 
        (SELECT [Fin_Year] - 1
        FROM tbl.Calendar
        WHERE Full_Date = CAST(GETDATE() AS DATE)))


SELECT DISTINCT MIN(CAST(Data_Table_Date_Column AS DATE)) AS Min_Date,
    MAX(CAST(Data_Table_Date_Column AS DATE)) AS Max_Date



FROM data_Table
WHERE CAST(Data_Table_Date_Column AS DATE) >= @MinDate

Msg 156, Level 15, State 1, Procedure how, Line 3 Incorrect syntax near the keyword 'DECLARE'.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
James Anon
  • 31
  • 1
  • 6
  • 2
    You cannot declare `variables` in view definitions. – Yogesh Sharma Nov 01 '17 at 09:21
  • As @YogeshSharma states, you cannot declare variables in views. You can however use a user-defined function. Look her https://stackoverflow.com/questions/6114826/sql-views-no-variables – SqlKindaGuy Nov 01 '17 at 09:27

2 Answers2

1

You cannot declare variables in view definitions. But the alternate way is :

CREATE VIEW View_I_Want
AS
     SELECT DISTINCT
            MIN(CAST(Data_Table_Date_Column AS DATE)) AS Min_Date,
            MAX(CAST(Data_Table_Date_Column AS DATE)) AS Max_Date
     FROM data_Table
     WHERE CAST(Data_Table_Date_Column AS DATE) >=
     (
         SELECT MIN(Full_Date) AS First_Date
         FROM tbl.Calendar
         WHERE [Fin_Year] =
         (
             SELECT [Fin_Year] - 1
             FROM tbl.Calendar
             WHERE Full_Date = CAST(GETDATE() AS DATE)
         )
     );
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
0

Do you want the variable as a parameter for your stored procedure? in that case you dont use the 'Declare' but specify the datatype if you want it as a variable within the procedure use Declare @variableName AS datatype 

CREATE PROCEDURE dbo.YourStoredProcedure (
@parameter INT )
AS
BEGIN
DECLARE @variable1 AS INT
DECLARE @variable2 AS INT

....

END
holder
  • 585
  • 2
  • 9