sql-server - SQL IF 语句语法错误：语句中的 END 错误

Question

我对我的 SQL 有点生疏，但试图做一个简单的 IF 语句。我想做 IF ELSE 但出错了，所以现在只想做简单的比较。

代码：

 --USE [AsurionMobility]
--GO
--/****** Object:  StoredProcedure [dbo].[usp_RecipientDomain_Report]    Script Date: 8/6/2020 1:15:16 PM ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO

---- =============================================
---- Author:        <Author,,Name>
---- Create date: <Create Date,,>
---- Description:   <Description,,>
---- =============================================
--ALTER PROCEDURE [dbo].[usp_RecipientDomain_Report]
DECLARE
    @startdate smalldatetime = '07-27-2020',
    @enddate smalldatetime = GETDATE(),
    @ClientName AS NVARCHAR(100) = 'Verizon'
--AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @EmailStatus AS TABLE(Domain nvarchar(50), TotalRecords int, TotalBounces int)

    DECLARE @SpecifiedDomains AS TABLE(Domain nvarchar(50), SortOrder int)

    IF @ClientName = 'Verizon' BEGIN
        INSERT INTO @SpecifiedDomains VALUES
        ('AOL.com'          ,10),
        ('Yahoo.com'        ,20),
        ('SBCGlobal.net'    ,30),
        ('Gmail.com'        ,40),
        ('Hotmail.com'      ,50),
        ('Comcast.net'      ,60), 
        ('GE.com'           ,70), 
        ('Verizon.net'      ,80), 
        ('ATT.net'          ,90), 
        ('MSN.com'          ,100),
        ('ATT.com'          ,110),
        ('Misc ISP.'        ,1000)

        ;WITH baseRecords AS 
        (
            SELECT  
                CASE
                    WHEN RIGHT(Email1, LEN(Email1) - CHARINDEX('@', email1)) IN (SELECT Domain FROM @SpecifiedDomains) 
                    THEN UPPER(RIGHT(Email1, LEN(Email1) - CHARINDEX('@', email1)))
                    ELSE 'Misc ISP.'
                END AS Domain,
                EmailStatus
            FROM    
                [dbo].[Verizon_Recipients] R
                INNER JOIN DataFile D           
                ON R.OriginalFileID = D.DataFileID
                INNER JOIN EmailPrograms EP
                ON R.EmailProgramID = EP.EmailProgramID
                INNER JOIN Clients C
                ON EP.ClientID = C.ClientID
            WHERE
                D.DataFileDate BETWEEN @startdate AND @enddate AND
                R.OutputFileID IS NOT NULL AND 
                R.EmailStatus IS NOT NULL AND 
                C.ClientName = @ClientName
        )
    END 
    
    IF @ClientName = 'AT&T' BEGIN 
        INSERT INTO @SpecifiedDomains VALUES
        ('AOL.com'          ,10),
        ('Yahoo.com'        ,20),
        ('SBCGlobal.net'    ,30),
        ('Gmail.com'        ,40),
        ('Hotmail.com'      ,50),
        ('Comcast.net'      ,60), 
        ('GE.com'           ,70), 
        ('Verizon.net'      ,80), 
        ('ATT.net'          ,90), 
        ('MSN.com'          ,100),
        ('ATT.com'          ,110),
        ('Misc ISP.'        ,1000)

        ;WITH baseRecords AS 
        (
            SELECT  
                CASE
                    WHEN RIGHT(Email1, LEN(Email1) - CHARINDEX('@', email1)) IN (SELECT Domain FROM @SpecifiedDomains) 
                    THEN UPPER(RIGHT(Email1, LEN(Email1) - CHARINDEX('@', email1)))
                    ELSE 'Misc ISP.'
                END AS Domain,
                EmailStatus
            FROM    
                [dbo].[ATT_Recipients] R
                INNER JOIN DataFile D           
                ON R.OriginalFileID = D.DataFileID
                INNER JOIN EmailPrograms EP
                ON R.EmailProgramID = EP.EmailProgramID
                INNER JOIN Clients C
                ON EP.ClientID = C.ClientID
            WHERE
                D.DataFileDate BETWEEN @startdate AND @enddate AND
                R.OutputFileID IS NOT NULL AND 
                R.EmailStatus IS NOT NULL AND 
                C.ClientName = @ClientName
        )
    END 

    IF @ClientName <> 'AT&T' AND @ClientName <> 'Verizon' BEGIN
        INSERT INTO @SpecifiedDomains VALUES
        ('AOL.com'          ,10),
        ('Yahoo.com'        ,20),
        ('SBCGlobal.net'    ,30),
        ('Gmail.com'        ,40),
        ('Hotmail.com'      ,50),
        ('Comcast.net'      ,60), 
        ('GE.com'           ,70), 
        ('Verizon.net'      ,80), 
        ('ATT.net'          ,90), 
        ('MSN.com'          ,100),
        ('ATT.com'          ,110),
        ('Misc ISP.'        ,1000)

        ;WITH baseRecords AS 
        (
            SELECT  
                CASE
                    WHEN RIGHT(Email1, LEN(Email1) - CHARINDEX('@', email1)) IN (SELECT Domain FROM @SpecifiedDomains) 
                    THEN UPPER(RIGHT(Email1, LEN(Email1) - CHARINDEX('@', email1)))
                    ELSE 'Misc ISP.'
                END AS Domain,
                EmailStatus
            FROM    
                [dbo].[Recipients] R
                INNER JOIN DataFile D           
                ON R.OriginalFileID = D.DataFileID
                INNER JOIN EmailPrograms EP
                ON R.EmailProgramID = EP.EmailProgramID
                INNER JOIN Clients C
                ON EP.ClientID = C.ClientID
            WHERE
                D.DataFileDate BETWEEN @startdate AND @enddate AND
                R.OutputFileID IS NOT NULL AND 
                R.EmailStatus IS NOT NULL AND 
                C.ClientName = @ClientName
        )
    END

它在所有语句的“结束”上给了我一个错误。我读了一些建议我需要使用 SELECT 语句的帖子？我尝试使用 SELECT 但仍然出现语法错误。对不起，自从我做了 SQL IF 语句以来已经有一段时间了。我不确定动态 SQL 是否是最佳选择？

标签： sql-servertsql

Answer 1

正如damien和larnu所指出的，您必须首先修复您的 CTE，如下面的查询。

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)  
AS  
-- Define the CTE query.  
(  
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
)  
-- Define the outer query referencing the CTE name.  
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear  
FROM Sales_CTE  
GROUP BY SalesYear, SalesPersonID  
ORDER BY SalesPersonID, SalesYear; 

有关 CTE 语法的更多信息，请访问 microsoft 的此链接。