I have this global cursor that is created by a string like this, however when execute, I get this error message :
A cursor with the name 'crsDTO' does not exist.
Code:
DECLARE @Cursor NVARCHAR(MAX);
SET @Cursor = 'DECLARE crsDTO CURSOR FOR SELECT p.ID, p.Price, p.Count FROM Business.Products';
exec sp_executesql @Cursor;
OPEN crsDTO; -- fails here <<<<<<<<
BEGIN TRY
FETCH NEXT FROM crsDTO INTO @ID, @Price, @Count;
WHILE 0 = @@fetch_status
BEGIN
PRINT(@ID)
FETCH NEXT FROM crsDTO INTO @ID, @Price, @Count;
END;
CLOSE crsDTO;
DEALLOCATE crsDTO;
END TRY
BEGIN CATCH
CLOSE crsDTO;
DEALLOCATE crsDTO;
END CATCH
I looked around everything looks to be fine.. and I can't find why it's not working.
UPDATE
This SP is going to bulk update either price or stock or both. i might be wrong and there might be alternative way which is much better than this i am open to all correction. However this cursor is going to be filtered based on user opinion. it can change stock/prices(as percentage amount or basic amount) based on the filters. so for example user wants bulk change the prices for only specific brandId or combination of BrandId/CategoryId and SupplierId or none of them(which means every product).
CREATE procedure [Business].[Product_BulkUpdate]
(
@PO_Error int OUTPUT,
@PO_ErrorMessage Nvarchar(Max) OUTPUT,
@PO_Step int OUTPUT,
@CallerUserId uniqueidentifier,
@CategoryId uniqueidentifier = null,
@BrandId uniqueidentifier = null,
@SupplierId uniqueidentifier = null,
@ProductName nvarchar(max) = null,
@Amount float = null,
@AmountPercentage float = null,
@IsInStock bit = null
)
as
DECLARE @ID Uniqueidentifier;
DECLARE @Price int;
DECLARE @Count int;
DECLARE @KW nvarchar(max);
DECLARE @Cursor nvarchar(max);
DECLARE @WhereClause nvarchar(max);
set @WhereClause = ' 1=1 ';
if (@ProductName is not null)
set @WhereClause =@WhereClause + ' And p.Name like N'''+'%'+cast(@ProductName as nvarchar(4000))+'%'+''' ';
if (@CategoryId is not null)
set @WhereClause =@WhereClause + ' And c.ID in (SELECT cf.id FROM Business.GetCategoryChilds('''+CAST(@CategoryId as nvarchar(50)) +''') cf) ';
if(@SupplierId is not null)
set @WhereClause = @WhereClause + ' AND p.SupplierId in (' + CAST(@SupplierId as nvarchar(50)) + ') ';
IF(@BrandId is not null)
set @WhereClause = @WhereClause + ' AND bb.ID in (' + CAST(@BrandId as nvarchar(50)) + ')';
SET @Cursor = ' DECLARE crsDTO cursor for
SELECT p.ID, p.Price, p.Count FROM Business.Products p
INNER JOIN Kernel.BaseEntity b on b.ID = p.ID AND b.IsDelete = 0
LEFT JOIN Business.Brand bb on bb.ID = p.BrandId
LEFT JOIN Business.Category c on c.ID = p.CategoryId
LEFT JOIN MarketPlace.Supplier s on s.SupplierId = p.SupplierId
WHERE '+@WhereClause+' AND c.CategoryTypeId = 10700';
begin
--- Auto generated procedure
SET NOCOUNT ON;
SET @PO_Error = 0;
SET @PO_Step = 0;
SET @PO_ErrorMessage = '';
BEGIN TRY
exec sp_executesql @Cursor;
SET @PO_Step = 1;
OPEN crsDTO;
BEGIN TRY
FETCH NEXT FROM crsDTO INTO @ID, @Price, @Count;
while 0 = @@fetch_status
BEGIN
IF(@IsInStock = 0) BEGIN
IF(@Amount is not null and @AmountPercentage is null) BEGIN
IF EXISTS (SELECT ID FROM Business.Products WHERE ID = @ID) BEGIN
UPDATE Business.Products SET
Price = @Price + @Amount
WHERE ID = @ID
END
END else IF(@AmountPercentage is not null and @Amount is null) BEGIN
IF EXISTS (SELECT ID FROM Business.Products WHERE ID = @ID) BEGIN
UPDATE Business.Products SET
Price = (@Price * (@AmountPercentage / 100))
WHERE ID = @ID
END
END
END ELSE IF(@IsInStock = 1) BEGIN
IF(@Amount is not null and @AmountPercentage is null) BEGIN
IF EXISTS (SELECT ID FROM Business.Products WHERE ID = @ID) BEGIN
UPDATE Business.Products SET
Price = @Price + @Amount,
Count = 0
WHERE ID = @ID
END
END else IF(@AmountPercentage is not null and @Amount is null) BEGIN
IF EXISTS (SELECT ID FROM Business.Products WHERE ID = @ID) BEGIN
UPDATE Business.Products SET
Price = (@Price * (@AmountPercentage / 100)),
Count = 0
WHERE ID = @ID
END
END ELSE IF(@Amount is null and @AmountPercentage is null) BEGIN
IF EXISTS (SELECT ID FROM Business.Products WHERE ID = @ID) BEGIN
UPDATE Business.Products SET
Count = 0
WHERE ID = @ID
END
END
END
SET @PO_Step = 2;
FETCH NEXT FROM crsDTO INTO @ID, @Price, @Count;
END;
CLOSE crsDTO;
DEALLOCATE crsDTO;
END TRY
BEGIN CATCH
CLOSE crsDTO;
DEALLOCATE crsDTO;
SET @PO_Error = ERROR_NUMBER();
SET @PO_ErrorMessage = ERROR_MESSAGE();
END CATCH
END TRY
BEGIN CATCH
SET @PO_Error = ERROR_NUMBER();
SET @PO_ErrorMessage = ERROR_MESSAGE();
END CATCH
END;
I would add check if cursor exists:
-- ....
BEGIN CATCH
IF CURSOR_STATUS('global','crsDTO')>=-1
BEGIN
CLOSE crsDTO;
DEALLOCATE crsDTO;
END
END CATCH
Using global cursor/row-by-row approach does not seems to be the best solution.