Used Cursor inside cursor for update it is showing o rows effected, logic is working when i tried manually

stored procedure
Used Cursor inside cursor for update it is showing 0 rows effected, logic is working when i tried manually, declaring and closing done properly.

any changes do i need to do
or any alternatives than cursor.

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
ALTER PROCEDURE [dbo].[POS_Discount_Report]
@OutletId INT = NULL,
@FromDate DATE = NULL,
@ToDate DATE = NULL,
@DiscountPercent DECIMAL = NULL
AS
begin
SELECT @CutOffInvoiceAmount = AVG(InvoiceAmount) FROM POS_SalesReceiptMaster WHERE StampDate BETWEEN @FromDate AND @ToDate

DECLARE Receipt_cursor CURSOR FOR
SELECT Id FROM POS_SalesReceiptMaster WHERE StampDate BETWEEN @FromDate AND @ToDate AND InvoiceAmount <= @CutOffInvoiceAmount
OPEN Receipt_cursor
FETCH NEXT FROM Receipt_cursor
INTO @ReceiptId

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE Item_cursor CURSOR FOR
SELECT Id FROM Updated_SalesReceiptItems WHERE ReceiptId = @ReceiptId
OPEN Item_cursor
FETCH NEXT FROM Item_cursor
INTO @ID


WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Percentage = Percentage, @ItemPrice = Price FROM
Updated_SalesReceiptItems WHERE Id = @ID
IF @Percentage = 5
BEGIN
SELECT @UpdatePercentage = Tax5 FROM Updated_Master
Where Percentage = @DiscountPercent
END
ELSE
BEGIN
@UpdatePercentage = 5
END



UPDATE Updated_SalesReceiptItems
SET ProductId = Product.ProductId,
Actualprice = Product.Actualprice,
Quantity = Product.Qty,
ProductName = Product.ProductName,
unit = Product.unit,
CategoryName= Product.CategoryName,
Percentage= Product.Percentage,
Amount = Product.Amount FROM
(SELECT TOP 1 PM.ProductId, ProductCode,

dbo.fn_Get_ProductPrice_By_Outlet(ProductId,@OutletId)
AS
Actualprice,
(CASE WHEN ( dbo.fn_Get_ProductPrice_By_Outlet(@OutletId, ProductId) != 0)
THEN (@ItemPrice / dbo.fn_Get_ProductPrice_By_Outlet(@OutletId, ProductId))
ELSE 0
END) AS Qty,
ProductName, Unit, CategoryName, @UpdatePercentage AS Percentage,
dbo.fn_Get_ProductPrice_By_Outlet(@OutletId, ProductId) * (@UpdatePercentage/100) AS TaxAmount
FROM dbo.Products_Master PM
INNER JOIN ProductCategory_Master CM ON PM.CategoryId = CM.CategoryId
INNER JOIN tax_master TM ON PM.TaxId = TM.Id
WHERE (@ItemPrice) % nullif(dbo.fn_Get_ProductPrice_By_Outlet(@OutletId, ProductId),0) = 0
AND Percentage = @UpdatePercentage) Product
WHERE Id = @ID
end



FETCH NEXT FROM Item_cursor
INTO @ID
END
CLOSE Item_cursor;
DEALLOCATE Item_cursor;


FETCH NEXT FROM Receipt_cursor
INTO @ReceiptId
END
CLOSE Receipt_cursor;
DEALLOCATE Receipt_cursor;

END


1 Answer
1

Okay, this is pretty scrappy, and probably won't work without some fixing, but it should give you the general pattern for doing all of this in a single query?

ALTER PROCEDURE POS_Discount_Report (
@OutletId INT = NULL,
@FromDate DATE = NULL,
@ToDate DATE = NULL,
@DiscountPercent DECIMAL = NULL)
AS
BEGIN
DECLARE @CutOffInvoiceAmount NUMERIC(19,2); --?? seems to be missing from original procedure
SELECT @CutOffInvoiceAmount = AVG(InvoiceAmount) FROM POS_SalesReceiptMaster WHERE StampDate BETWEEN @FromDate AND @ToDate; --What happens if one or both of these is NULL?

--CTEs
WITH Receipt AS (
SELECT Id FROM POS_SalesReceiptMaster WHERE StampDate BETWEEN @FromDate AND @ToDate AND InvoiceAmount <= @CutOffInvoiceAmount),
Item AS (
SELECT Id FROM Updated_SalesReceiptItems s INNER JOIN Receipt r ON s.ReceiptId = r.Id),
PercentQuery AS (
SELECT i.Id, u.[Percentage], u.Price FROM Updated_SalesReceiptItems u INNER JOIN Item i ON u.Id = i.Id),
UpdatePercent AS (
SELECT p.Id, p.[Percentage], p.Price, CASE WHEN p.[Percentage] = 5 THEN u.Tax5 ELSE 5 END AS UpdatePercentage FROM PercentQuery p INNER JOIN Updated_Master u ON u.[Percentage] = @DiscountPercent)
UPDATE
u
SET
ProductId = pm.ProductId,
Actualprice = dbo.fn_Get_ProductPrice_By_Outlet(ProductId, @OutletId),
Quantity =
CASE
WHEN (dbo.fn_Get_ProductPrice_By_Outlet(@OutletId, ProductId) != 0)
THEN (@ItemPrice / dbo.fn_Get_ProductPrice_By_Outlet(@OutletId, ProductId))
ELSE 0
END,
ProductName = pm.ProductName,
unit = pm.unit, --not sure on the alias here, as it's missing in the original query
CategoryName = pm.CategoryName,
[Percentage] = u.UpdatePercentage,
Amount = dbo.fn_Get_ProductPrice_By_Outlet(@OutletId, ProductId) * (u.UpdatePercentage / 100) --although this was TaxAmount originally??!
FROM
dbo.Products_Master pm
INNER JOIN ProductCategory_Master cm ON cm.CategoryId = pm.CategoryId
INNER JOIN tax_master tm ON tm.Id = pm.TaxId
INNER JOIN UpdatePercent up ON up.Id = pm.Id
INNER JOIN Updated_SalesReceiptItems u ON u.Id = up.Id
WHERE
(p.Price) % NULLIF(dbo.fn_Get_ProductPrice_By_Outlet(@OutletId, pm.ProductId), 0) = 0
AND [Percentage] = UpdatePercentage;
END;


Basically, I use nested common-table expressions to perform the same action as your original cursors, but these are now set-based. This means I can JOIN the results to the table to be updated and perform all of the updates in a single hit.

I almost certainly got some of this wrong, as I could see a number of parts in your original query that just seemed incorrect?

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.