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
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 OR
s):
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With