If-Else Condition inside cursor

Question

My Current SQL Syntax is something like

Declare CursorName CURSOR FOR
 Select Query

Now the select query would contain an If-Else Condition.

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

How to write second If-Else statement inside a cursor in SQL Server?

please help! Let me know for my inputs.!!

My Original Query

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
                       )

BEGIN

    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

END

My If-Else Query

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

FYI, if you are needing to use an if/else construct inside a cursor you may want to re-evaluate your process...this is not something SQL is optimized for. — JNK, Jan 24 '11 at 14:02
@JNK: That would be fine with me.. let me know what correction needs to be done with regard to SQL syntax.. — xorpower, Jan 24 '11 at 14:02
SQL Syntax # 2 (If-Else) to be fit in SQL Syntax # 1 (Cursor) — xorpower, Jan 24 '11 at 14:05
@Romil - I think you misunderstand what I am asking - I am hoping there is an alternative method to get the result you are after **WITHOUT** using the if/else and cursor. What exactly are you trying to do? — JNK, Jan 24 '11 at 14:07
I need to use both If-Else Select Query and that too with cursor. Can't we have both together? — xorpower, Jan 24 '11 at 14:09
@Romil - I don't think so as the `SELECT` is part of the declaration itself. Why do you need to do this? There may well be a better way than using a cursor at all. — Martin Smith, Jan 24 '11 at 14:14
@Romil Nagrani - What Martin and I are saying is, there is likely a better way to get the result you want. Can you tell us what your data is like and why you think it needs to run this way? — JNK, Jan 24 '11 at 14:19

score 16 · Accepted Answer · answered Jan 24 '11 at 14:23

for as much as I HATE cursors, try this:

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

From your code it looks like you have dynamic search conditions. The key with a dynamic search conditions is to make sure an index is used, instead of how can I easily reuse code, eliminate duplications in a query, or try to do everything with the same query. Here is a very comprehensive article on how to handle this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

It covers all the issues and methods of trying to write queries with multiple optional search conditions. This main thing you need to be concerned with is not the duplication of code, but the use of an index. If your query fails to use an index, it will preform poorly. There are several techniques that can be used, which may or may not allow an index to be used.

here is the table of contents:

  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)

and optimize it at run time to be (provided that only @Search2 was passed in with a value):

WHERE
    Column2=@Search2

and an index can be used (if you have one defined on Column2)

Lol! I didn't even try that as I assumed it would moan about the multiple declarations (+1) — Martin Smith, Jan 24 '11 at 14:25
Hi is there another way to iterate each row of a select query except cursor? — guradio, Jul 15 '19 at 01:23
@guradio, look here https://stackoverflow.com/a/1578248/65223 you can do a loop with a simple select of one row each iteration. This linked example loops over a table, but you can do something similar without a table. — KM., Aug 05 '19 at 17:33

score 0 · Answer 2 · answered Jan 24 '11 at 14:10

I would change the main SELECT for the cursor so that it uses a CASE statement along with a table variable to set a char field that would flag each record as a certain type.

For example, something like:

DECLARE @Stuff TABLE
(
    ID int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
    SystemID int,
    Flag char
)


INSERT INTO @Stuff (SystemID, Flag)
SELECT 
    SystemID,

    CASE SomeFieldInMainTable
        WHEN 1 THEN 'A'
        WHEN 2 THEN 'B'
        WHEN 3 THEN 'C'
        ELSE 0
    END

FROM TheTable

This would let you create a subset of whatever table you're using the cursor to step through, and "tag" records in that subset based on the CASE logic appropriate for your subqueries, but you only need hit the main table with one big SELECT rather than the separate ones within the IF blocks.

After the table variable is populated, you can then query it separately to grab the needed data extracted from the main table.

If-Else Condition inside cursor

2 Answers2