视图中DECLARE关键字附近语法错误，求解决方法

Hey there, let's break down why you're hitting that error and how to fix it quickly!

The Root Cause #

SQL Server views don't allow DECLARE statements or local variables — views are essentially stored SELECT queries, and they can only contain a single, self-contained query (no procedural logic like variable declarations or SET commands). That's exactly why your code is throwing a syntax error when you try to declare @StartDate and @EndDate inside the view.

Solutions to Fix the Query #

Your goal is to pull data from the previous calendar month, so we can rewrite the query to calculate that date range directly without using variables. Here are a few clean, working options:

Option 1: Embed Date Calculations Directly in the HAVING Clause #

This is the simplest fix — just replace your variable references with the date calculation logic you already have:

SELECT dbo.General_Ledger_Detail.Accounting_ID, 
       dbo.General_Ledger_Detail.Cost_Centre, 
       dbo.General_Ledger_Detail.Product_ID, 
       dbo.General_Ledger_Detail.Accounted_Amount AS Amount, 
       dbo.General_Ledger_Detail.Account_Name, 
       dbo.General_Ledger_Detail.Accounting_Date, 
       dbo.Account_Codes_Sales_OPEX$.[Opex Type], 
       dbo.LogSolOpexCC.Logistic_Solutions_Type
FROM dbo.General_Ledger_Detail
INNER JOIN dbo.Account_Codes_Sales_OPEX$ ON dbo.General_Ledger_Detail.Accounting_ID = dbo.Account_Codes_Sales_OPEX$.[Account Code]
INNER JOIN dbo.LogSolOpexCC ON dbo.General_Ledger_Detail.Cost_Centre = dbo.LogSolOpexCC.Cost_Centre
GROUP BY dbo.General_Ledger_Detail.Accounting_ID, 
         dbo.General_Ledger_Detail.Cost_Centre, 
         dbo.General_Ledger_Detail.Product_ID, 
         dbo.General_Ledger_Detail.Accounted_Amount, 
         dbo.General_Ledger_Detail.Account_Name, 
         dbo.General_Ledger_Detail.Accounting_Date, 
         dbo.Account_Codes_Sales_OPEX$.[Opex Type], 
         dbo.LogSolOpexCC.Logistic_Solutions_Type
HAVING (dbo.General_Ledger_Detail.Accounting_Date BETWEEN 
        DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) 
        AND 
        DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
       )

Option 2: Use a CTE for Cleaner Date Logic #

If you want more readable code (especially if you ever need to adjust the date range later), use a Common Table Expression (CTE) to calculate the date range first:

WITH DateRange AS (
    SELECT 
        StartDate = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0),
        EndDate = DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
)
SELECT dbo.General_Ledger_Detail.Accounting_ID, 
       dbo.General_Ledger_Detail.Cost_Centre, 
       dbo.General_Ledger_Detail.Product_ID, 
       dbo.General_Ledger_Detail.Accounted_Amount AS Amount, 
       dbo.General_Ledger_Detail.Account_Name, 
       dbo.General_Ledger_Detail.Accounting_Date, 
       dbo.Account_Codes_Sales_OPEX$.[Opex Type], 
       dbo.LogSolOpexCC.Logistic_Solutions_Type
FROM dbo.General_Ledger_Detail
INNER JOIN dbo.Account_Codes_Sales_OPEX$ ON dbo.General_Ledger_Detail.Accounting_ID = dbo.Account_Codes_Sales_OPEX$.[Account Code]
INNER JOIN dbo.LogSolOpexCC ON dbo.General_Ledger_Detail.Cost_Centre = dbo.LogSolOpexCC.Cost_Centre
CROSS JOIN DateRange
GROUP BY dbo.General_Ledger_Detail.Accounting_ID, 
         dbo.General_Ledger_Detail.Cost_Centre, 
         dbo.General_Ledger_Detail.Product_ID, 
         dbo.General_Ledger_Detail.Accounted_Amount, 
         dbo.General_Ledger_Detail.Account_Name, 
         dbo.General_Ledger_Detail.Accounting_Date, 
         dbo.Account_Codes_Sales_OPEX$.[Opex Type], 
         dbo.LogSolOpexCC.Logistic_Solutions_Type
HAVING (dbo.General_Ledger_Detail.Accounting_Date BETWEEN DateRange.StartDate AND DateRange.EndDate)

Option 3: Convert to an Inline Table-Valued Function (For Reusability) #

If you need to reuse this logic often, or want to make it parameterizable later, turn it into an inline table-valued function (it acts like a view but supports procedural logic):

CREATE FUNCTION dbo.GetLastMonthOpexData()
RETURNS TABLE
AS
RETURN (
    WITH DateRange AS (
        SELECT 
            StartDate = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0),
            EndDate = DATEADD(mm, 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0))
    )
    SELECT dbo.General_Ledger_Detail.Accounting_ID, 
           dbo.General_Ledger_Detail.Cost_Centre, 
           dbo.General_Ledger_Detail.Product_ID, 
           dbo.General_Ledger_Detail.Accounted_Amount AS Amount, 
           dbo.General_Ledger_Detail.Account_Name, 
           dbo.General_Ledger_Detail.Accounting_Date, 
           dbo.Account_Codes_Sales_OPEX$.[Opex Type], 
           dbo.LogSolOpexCC.Logistic_Solutions_Type
    FROM dbo.General_Ledger_Detail
    INNER JOIN dbo.Account_Codes_Sales_OPEX$ ON dbo.General_Ledger_Detail.Accounting_ID = dbo.Account_Codes_Sales_OPEX$.[Account Code]
    INNER JOIN dbo.LogSolOpexCC ON dbo.General_Ledger_Detail.Cost_Centre = dbo.LogSolOpexCC.Cost_Centre
    CROSS JOIN DateRange
    GROUP BY dbo.General_Ledger_Detail.Accounting_ID, 
             dbo.General_Ledger_Detail.Cost_Centre, 
             dbo.General_Ledger_Detail.Product_ID, 
             dbo.General_Ledger_Detail.Accounted_Amount, 
             dbo.General_Ledger_Detail.Account_Name, 
             dbo.General_Ledger_Detail.Accounting_Date, 
             dbo.Account_Codes_Sales_OPEX$.[Opex Type], 
             dbo.LogSolOpexCC.Logistic_Solutions_Type
    HAVING (dbo.General_Ledger_Detail.Accounting_Date BETWEEN DateRange.StartDate AND DateRange.EndDate)
)

To use this function, just run:

SELECT * FROM dbo.GetLastMonthOpexData()

Final Notes #

All these approaches avoid using DECLARE statements, which fixes the syntax error while keeping your original business logic intact. Pick the option that best fits how you plan to use this query!

内容的提问来源于stack exchange，提问作者CB12