sql-server - 游标内的 If-Else 条件

Question

我当前的SQL语法类似于

Declare CursorName CURSOR FOR
 Select Query

现在，选择查询将包含If-Else条件。

If @Parameter1 is NULL
 BEGIN
  Select-Query1
 END
ELSE
 BEGIN
  Select-Query2
 END

如何在SQL Server的游标中写入第二条If-Else语句？

请帮忙!
让我知道我的意见!!

我的原始查询

Create Table #TempTable(PlanID BIGINT,PlanName NVARCHAR(50),InsuranceCompany Nvarchar(100),CurrentBalance DECIMAL(14,2),
                        [30DaysBalance] DECIMAL(14,2),[60DaysBalance] DECIMAL(14,2),[90DaysBalance] Decimal(14,2),
                        [120DaysBalance] DECIMAL(14,2),[150DaysBalance] Decimal(14,2),CurrentDaysPlanAmount DECIMAL(14,2),
                        [30DaysPlanAmount] DECIMAL(14,2),[60DaysPlanAmount] DECIMAL(14,2),[90DaysPlanAmount] Decimal(14,2),
                        [120DaysPlanAmount] DECIMAL(14,2),[150DaysPlanAmount] Decimal(14,2),StartDate DateTime,EndDate DateTime
                       )

开始

    Declare @BillID BIGINT,@PatientID BIGINT,@BillDetailID BIGINT,@SendDt DateTime

    Declare Cursor_Claim_PlanAgingReport Cursor 

    For Select Bill.BillID,Bill.PatientID,BillDetail.BillDetailID,Claim.SendDt From Bill Inner Join
        BillDetail On Bill.BillID = BillDetail.BillID Inner Join
        Claim on Bill.BillID = Claim.BillID Left Outer Join
        Payment On Bill.BillID = Payment.BillID 
        Where 
        ---Payment.BillID Is Null  AND
        Claim.SendDt 
        Between @StartDt AND @EndDt 
        ---And Claim.Status = 'Sent' 
        AND Claim.Status = 'Resent'



    Open Cursor_Claim_PlanAgingReport

    FETCH NEXT FROM Cursor_Claim_PlanAgingReport INTO @BillID,@PatientID,@BillDetailID,@SendDt

    While @@FETCH_STATUS = 0
    BEGIN

        Insert Into #TempTable SELECT Distinct(vwAgingPlan.PlanID),vwAgingPlan.Plan_Name,vwAgingPlan.Insurance_Company,         

        --// Current Balance --
        IsNull((SELECT top 1 vwAgingPlan.Copay as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.SendDt,getDate()) < 30 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID), 0) AS CurrentBalance,

        --// [30DaysBalance] --
        IsNull((SELECT top 1 vwAgingPlan.Copay as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.SendDt,getDate()) > 30 AND DATEDIFF("dd", vwAgingPlan.SendDt,getDate()) <= 60 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID), 0) AS [30DaysBalance],

        --// [60DaysBalance] --
        IsNull((SELECT top 1 vwAgingPlan.Copay as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.SendDt,getDate()) > 60 AND DATEDIFF("dd", vwAgingPlan.SendDt,getDate()) <= 90 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID), 0) AS [60DaysBalance],

        --// [90DaysBalance] --
        IsNull(
            (SELECT  top 1vwAgingPlan.Copay as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.SendDt,getDate()) > 90 AND DATEDIFF("dd", vwAgingPlan.SendDt,getDate()) <= 120 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID), 0) AS [90DaysBalance],

        --// [120DaysBalance] --
        IsNull((SELECT top 1 vwAgingPlan.Copay as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.SendDt,getDate()) > 120 AND DATEDIFF("dd", vwAgingPlan.SendDt,getDate()) <= 150 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID), 0) AS [120DaysBalance],

        --// [150DaysBalance] --
        IsNull((SELECT top 1 vwAgingPlan.Copay as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.SendDt,getDate()) > 150 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID), 0) AS [150DaysBalance],

        IsNull((SELECT top 1 vwAgingPlan.PlanAmount as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.CreatedDt,getdate()) <= 30 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID),0) AS CurrentDaysPlanAmount,
        IsNull((SELECT top 1 vwAgingPlan.PlanAmount as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.CreatedDt, getdate()) > 30 AND DATEDIFF("dd", vwAgingPlan.CreatedDt,getdate()) <= 60 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID),0) AS [30DaysPlanAmount],
        IsNull((SELECT top 1 vwAgingPlan.PlanAmount as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.CreatedDt, getdate()) > 60 AND DATEDIFF("dd", vwAgingPlan.CreatedDt,getdate()) <= 90 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID),0) AS [60DaysPlanAmount],
        IsNull((SELECT top 1 vwAgingPlan.PlanAmount as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.CreatedDt, getdate()) > 90 AND DATEDIFF("dd", vwAgingPlan.CreatedDt,getdate()) <= 120 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID),0) AS [90DaysPlanAmount],
        IsNull((SELECT top 1 vwAgingPlan.PlanAmount as s from vwAgingPlan WHERE DATEDIFF("dd", vwAgingPlan.CreatedDt, getdate()) > 120 AND DATEDIFF("dd", vwAgingPlan.CreatedDt,getdate()) <= 150 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID),0) AS [120DaysPlanAmount],
        IsNull((SELECT top 1 vwAgingPlan.PlanAmount as s from vwAgingPlan WHERE DATEDIFF("dd",  vwAgingPlan.CreatedDt, getdate()) > 150 And vwAgingPlan.BillID = @BillID And vwAgingPlan.PatientID = @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID), 0) AS [150DaysPlanAmount] ,
        @StartDt,@EndDt
    FROM 
        vwAgingPlan
    WHERE
        vwAgingPlan.BillID = @BillID AND vwAgingPlan.PatientID= @PatientID AND vwAgingPlan.BillDetailID = @BillDetailID

    FETCH NEXT FROM Cursor_Claim_PlanAgingReport INTO @BillID,@PatientID,@BillDetailID,@SendDt
END


Close Cursor_Claim_PlanAgingReport
Deallocate Cursor_Claim_PlanAgingReport

Select * From #TempTable    

结尾

我的If-Else查询

IF @InsuranceName IS NULL


BEGIN
      SELECT Bill.BillID,
             Bill.PatientID,
             BillDetail.BillDetailID,
             Claim.SendDt,
             Claim.SendDT,
             InsurancePlan.Name
      FROM   Bill
             INNER JOIN BillDetail
               ON Bill.BillID = BillDetail.BillID
             INNER JOIN Claim
               ON Bill.BillID = Claim.BillID
             INNER JOIN Payment
               ON Bill.BillID = Payment.BillID
             INNER JOIN dbo.InsurancePlan
               ON dbo.BillDetail.PlanID = dbo.InsurancePlan.InsurancePlanID
             INNER JOIN dbo.InsuranceCompany
               ON dbo.InsurancePlan.InsuranceCompID = dbo.InsuranceCompany.InsuranceCompID
      WHERE   
        Claim.SendDt BETWEEN @StartDt AND @EndDt
          AND Claim.Status = 'Resent'
             --OR Claim.Status = 'Resent'

      PRINT 'No Insurance Name'
  END
ELSE
  BEGIN
      SELECT Bill.BillID,
             Bill.PatientID,
             BillDetail.BillDetailID,
             Claim.SendDt,
             Claim.SendDT,
             Claim.[Status],
             Payment.BillId AS PaymentBillID,
             InsurancePlan.Name
      FROM   Bill
             INNER JOIN BillDetail
               ON Bill.BillID = BillDetail.BillID
             INNER JOIN Claim
               ON Bill.BillID = Claim.BillID
             INNER JOIN Payment
               ON Bill.BillID = Payment.BillID
             INNER JOIN dbo.InsurancePlan
               ON dbo.BillDetail.PlanID = dbo.InsurancePlan.InsurancePlanID
             INNER JOIN dbo.InsuranceCompany
               ON dbo.InsurancePlan.InsuranceCompID = dbo.InsuranceCompany.InsuranceCompID
      WHERE  InsurancePlan.Name = @InsuranceName
             --AND Payment.BillID IS NULL
             AND Claim.SendDt BETWEEN @StartDt AND @EndDt
             AND Claim.[Status]='Resent'  

      PRINT 'Insurance Name: ' + @InsuranceName
  END 

Answer 1

就像我 讨厌 游标一样，试试这个:

DECLARE @FetchColumn varchar(10)

If @Parameter1 is NULL

BEGIN
    DECLARE YourCursor CURSOR FOR
        SELECT
            Column1
        FROM YourTable
        WHERE ...
        FOR READ ONLY
END
ELSE
BEGIN
    DECLARE YourCursor CURSOR FOR
        SELECT
            ColumnB
        FROM YourTable
        WHERE ...
        FOR READ ONLY
END
--populate and allocate resources to the cursor
OPEN YourCursor

--process each row
WHILE 1=1
BEGIN

    FETCH NEXT FROM YourCursor
        INTO @FetchColumn 

    --finished fetching all rows?
    IF @@FETCH_STATUS <> 0
    BEGIN --YES, all done fetching
        --exith the loop
        BREAK
    END --IF finished fetching

    --do something here--
    --do something here--
    PRINT @FetchColumn 

END --WHILE

--close and free the cursor's resources
CLOSE YourCursor
DEALLOCATE YourCursor

从您的代码看来，您有动态搜索条件。动态搜索条件的关键是确保使用索引，而不是如何轻松地重用代码、消除查询中的重复或尝试使用相同的查询执行所有操作。这是一篇关于如何处理这个主题的非常全面的文章:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

它涵盖了尝试使用多个可选搜索条件编写查询的所有问题和方法。您需要关注的主要事情不是代码的重复，而是索引的使用。如果您的查询无法使用索引，则执行效果会很差。可以使用多种技术，这些技术可能允许也可能不允许使用索引。

这是目录:

  Introduction      The Case Study: Searching Orders      The Northgale Database   Dynamic SQL      Introduction      Using sp_executesql      Using the CLR      Using EXEC()      When Caching Is Not Really What You Want   Static SQL      Introduction      x = @x OR @x IS NULL      Using IF statements      Umachandar's Bag of Tricks      Using Temp Tables      x = @x AND @x IS NOT NULL      Handling Complex Conditions   Hybrid Solutions – Using both Static and Dynamic SQL      Using Views      Using Inline Table Functions   Conclusion   Feedback and Acknowledgements   Revision History

if you are on the proper version of SQL Server 2008, there is an additional technique that can be used, see: Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)

If you are on that proper release of SQL Server 2008, you can just add OPTION (RECOMPILE) to the query and the local variable's value at run time is used for the optimizations.

Consider this, OPTION (RECOMPILE) will take this code (where no index can be used with this mess of ORs):

WHERE
    (@search1 IS NULL or Column1=@Search1)
    AND (@search2 IS NULL or Column2=@Search2)
    AND (@search3 IS NULL or Column3=@Search3)

并在运行时对其进行优化(前提是只有@Search2 传入了一个值):

WHERE
    Column2=@Search2

并且可以使用索引(如果您在 Column2 上定义了一个索引)