CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
James Anon
James Anon

Reputation: 31

Am I correct in thinking that you can not create a local variable?

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

Upvotes: 2

Views: 105

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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)
         )
     );

Upvotes: 1

holder
holder

Reputation: 585

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

Upvotes: 0

Related Questions