Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 132
  • Last Modified:

SQL cursor with storedProcedure

Hi
I have  a stored procedure that deletes and inserts a row based on the parameter.
I need to loop throug a selection of values and e\xecute the storedprocedure for each row.

I'm using a cursor now in the atempt to make it work, but it seems that the storedprocedure is not firering in the cursor.
When I run the SP With just one value it Works:
exec UpdateObservationAccumulator  2294796

but not in the cursor, dont get an error Message either

DECLARE @param INT

DECLARE curs CURSOR LOCAL  FOR
   select ObservationID from ObservationAccumulator
OPEN curs

FETCH NEXT FROM curs INTO @param

WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC UpdateObservationAccumulator @param
    --print @param
    FETCH NEXT FROM curs INTO @param
END

CLOSE curs
DEALLOCATE curs

I'm I doing something wrong or is it another better solution?
0
staleb
Asked:
staleb
  • 6
  • 5
  • 4
  • +2
1 Solution
 
Phillip BurtonCommented:
You need to post the FULL stored procedure. You are missing things such as the very beginning.

Also, how do you pass more than value to the stored procedure?
0
 
stalebAuthor Commented:
Hi the stored procedure Works fine when I execute it With just one value.

here is the storedprocedure:

Create PROCEDURE [dbo].[UpdateObservationAccumulator]
	(
		@ObservationID int

	)
AS
BEGIN

    DECLARE @Count int;
	DECLARE @CompanyID int;
	

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;
	
    SET NOCOUNT ON;

	DECLARE @SumFine DECIMAL = 0
	DECLARE @SumPayment DECIMAL = 0
	DECLARE @SumRePayment DECIMAL = 0
	DECLARE @SumCreditNote DECIMAL = 0
	DECLARE @SumDepreciation DECIMAL = 0
	DECLARE @SumInterestRate DECIMAL = 0
	DECLARE @SumBadDebt DECIMAL = 0
	DECLARE @SumFee DECIMAL = 0

	DECLARE @SumStep0 DECIMAL = 0
	DECLARE @SumStep1 DECIMAL = 0
	DECLARE @SumStep2 DECIMAL = 0
	DECLARE @SumStep3 DECIMAL = 0
	DECLARE @SumStep4 DECIMAL = 0
	DECLARE @SumStep5 DECIMAL = 0

	DECLARE @SumPM DECIMAL = 0
	DECLARE @PMTransactionCode INT
	DECLARE @AdjustingPMTransactionCode INT
	DECLARE @Regulation varchar(1)

	SELECT	@CompanyID=O.CompanyID, @Regulation = dbo.fnGetObservationRegulation(O.ObservationTypeID, O.ObservationTypeObjectID, null)
	FROM				Observation O 
	WHERE	ID_Observation = @ObservationID

	SELECT	@PMTransactionCode = (SELECT dbo.fnGetCompanyAutomationSetting(@CompanyID,'TCPolicyMakulering'))
	SELECT	@AdjustingPMTransactionCode = (SELECT AdjustingCTC FROM CompanyTransactionCode WHERE ID_TransactionCode = @PMTransactionCode)

	SELECT	@SumFine = @SumFine + CASE TT.TransactionTypeKey WHEN 0 THEN SL.Amount ELSE 0 END,
			@SumPayment = @SumPayment + CASE TT.TransactionTypeKey WHEN 1 THEN SL.Amount ELSE 0 END,
			@SumRePayment = @SumRePayment + CASE TT.TransactionTypeKey WHEN 2 THEN SL.Amount ELSE 0 END,
			@SumCreditNote = @SumCreditNote + CASE TT.TransactionTypeKey WHEN 3 THEN SL.Amount ELSE 0 END,
			@SumDepreciation = @SumDepreciation + CASE TT.TransactionTypeKey WHEN 4 THEN SL.Amount ELSE 0 END,
			@SumInterestRate = @SumInterestRate + CASE TT.TransactionTypeKey WHEN 5 THEN SL.Amount ELSE 0 END,
			@SumBadDebt = @SumBadDebt + CASE TT.TransactionTypeKey WHEN 6 THEN SL.Amount ELSE 0 END,
			@SumFee = @SumFee + CASE TT.TransactionTypeKey WHEN 7 THEN SL.Amount ELSE 0 END,
			@SumPM = @SumPM + CASE SL.TransactionCodeID WHEN @PMTransactionCode THEN SL.Amount ELSE 0 END,
			@SumPM = @SumPM + CASE SL.TransactionCodeID WHEN @AdjustingPMTransactionCode THEN SL.Amount ELSE 0 END
	FROM	Observation O INNER JOIN 
			SalesLedger SL ON O.ID_Observation = SL.ObservationID INNER JOIN 
			CompanyTransactionCode CTC ON CTC.ID_TransactionCode = SL.TransactionCodeID INNER JOIN 
			CompanyTransactionType CTT ON CTT.ID_CompanyTransactionType = CTC.CompanyTransactionTypeID INNER JOIN 
			TransactionType TT ON TT.ID_TransactionType = CTT.TransactionTypeID
	WHERE	SL.ObservationID = @ObservationID

	SELECT	@SumStep0 = dbo.fnGetSumStep(O.ID_Observation, 0, @Regulation),
			@SumStep1 = dbo.fnGetSumStep(O.ID_Observation, 1, @Regulation),
			@SumStep2 = dbo.fnGetSumStep(O.ID_Observation, 2, @Regulation),
			@SumStep3 = dbo.fnGetSumStep(O.ID_Observation, 3, @Regulation),
			@SumStep4 = dbo.fnGetSumStep(O.ID_Observation, 4, @Regulation),
			@SumStep5 = dbo.fnGetSumStep(O.ID_Observation, 5, @Regulation)
	FROM	Observation O 
	WHERE	O.ID_Observation = @ObservationID
	
	DELETE	FROM ObservationAccumulator WHERE ObservationID = @ObservationID
	
	INSERT INTO ObservationAccumulator
				(ObservationID, 
				SumIncome,
				SumPayment,
				SumRePayment,
				SumCreditNote,
				SumDepreciation,
				SumInterestRate,
				SumBadDebt,
				SumFee,
				SumPM,
				SumStep0,
				SumStep1,
				SumStep2,
				SumStep3,
				SumStep4,
				SumStep5)
	Values	(@ObservationID,
			@SumFine,
			@SumPayment,
			@SumRePayment,
			@SumCreditNote,
			@SumDepreciation,
			@SumInterestRate,
			@SumBadDebt,
			@SumFee,
			@SumPM,
			@SumStep0,@SumStep1,@SumStep2,@SumStep3,@SumStep4,@SumStep5)
END;

Open in new window

0
 
Phillip BurtonCommented:
OK, and how do you pass more than one value to the stored procedure?

It seems that you only have one value being passed into the procedure (as per the first line).
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
stalebAuthor Commented:
doesnt the cursor loop through every value?
WHILE @@FETCH_STATUS = 0 BEGIN
     EXEC UpdateObservationAccumulator @param
     --print @param
     FETCH NEXT FROM curs INTO @param
 END
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can do it without a cursor:
DECLARE @param INT
SET @param=0

WHILE (1 = 1)  -- Infinite looping
BEGIN  
        SELECT @param = MIN(ObservationID) 
        FROM ObservationAccumulator 
        WHERE ObservationID > @param

        IF @@ROWCOUNT = 0 BREAK; -- When reach end of table, exit the loop
        
         EXEC UpdateObservationAccumulator @param
END

Open in new window

0
 
Phillip BurtonCommented:
OK - I misunderstood the question.

What do you get from this:

DECLARE @param INT

DECLARE curs CURSOR LOCAL  FOR
   select ObservationID from ObservationAccumulator 
OPEN curs

FETCH NEXT FROM curs INTO @param

WHILE @@FETCH_STATUS = 0 
BEGIN
    --EXEC UpdateObservationAccumulator @param
    print @param
    FETCH NEXT FROM curs INTO @param
END

CLOSE curs
DEALLOCATE curs

Open in new window

0
 
ste5anSenior DeveloperCommented:
Hi the stored procedure Works fine when I execute it With just one value.

I seriously doubt that due to your wierd @@ROWCOUNT check. E.g. this simplified version runs nothing at all:

USE tempdb;
GO

CREATE PROCEDURE dbo.UpdateObservationAccumulator ( @ObservationID INT )
AS
    SET NOCOUNT ON;

    DECLARE @Count INT;
    DECLARE @CompanyID INT;      

    SET @Count = @@ROWCOUNT;
    IF @Count = 0
        RETURN;

    SELECT  @ObservationID;
GO

EXECUTE dbo.UpdateObservationAccumulator 1;
EXECUTE dbo.UpdateObservationAccumulator 2;
EXECUTE dbo.UpdateObservationAccumulator 3;
GO

DROP PROCEDURE dbo.UpdateObservationAccumulator;
GO

Open in new window

0
 
HuaMinChenBusiness AnalystCommented:
Please work like these

SET NOCOUNT ON;

DECLARE @vendor_id int, @vendor_name nvarchar(50),
    @message varchar(80), @product nvarchar(50);

PRINT '-------- Vendor Products Report --------';

DECLARE vendor_cursor CURSOR FOR 
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' '
    SELECT @message = '----- Products From Vendor: ' + 
        @vendor_name

    PRINT @message

        -- Get the next vendor.
    FETCH NEXT FROM vendor_cursor 
    INTO @vendor_id, @vendor_name
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Open in new window

0
 
ste5anSenior DeveloperCommented:
btw, there is no need for a cursor. Just use a table value parameter and to it set-based.
0
 
stalebAuthor Commented:
Hi
Philip: when I do the print I get a list of the ObservationId's


ste5an: it does work fine when I do it like this:
exec UpdateObservationAccumulator  2417273
the row gets updated just like I want it to.
The code is from a trigger made a couple of years ago.
It I tried to remove the "weird" code and it still doesnt work in the cursor
0
 
Phillip BurtonCommented:
OK - next step.

If you run this, you will get a list of the EXEC commands.

If you run the EXEC commands separately, does it work?

DECLARE @param INT

DECLARE curs CURSOR LOCAL  FOR
   select ObservationID from ObservationAccumulator 
OPEN curs

FETCH NEXT FROM curs INTO @param

WHILE @@FETCH_STATUS = 0 
BEGIN
    --EXEC UpdateObservationAccumulator @param
    print 'EXEC UpdateObservationAccumulator ' + convert(varchar(20), @param)
    FETCH NEXT FROM curs INTO @param
END

CLOSE curs
DEALLOCATE curs

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
@staleb
Did you try my solution without the cursor?

Also ste5an may have a point here referring to this part of code of yours SP:
SET @Count = @@ROWCOUNT;
    IF @Count = 0
        RETURN;

Open in new window

Why do you need it there?
0
 
stalebAuthor Commented:
Victor havent tried Yours yet, there was a lot of answeres coming, so I started With phillip.

But i tried to drop the storedprocedure and recreated it Without the rowcount as @ste5an said and now its working.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good. Later you can try mine solution to see if the performance is better but what's important that you have your issue solved.
0
 
stalebAuthor Commented:
@Vitor, yes I will try Yours to
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please do. I'm curious about the performance. Usually I avoid cursors :)
0
 
stalebAuthor Commented:
Hi
Victor testet Your solution, and there was very little if anny difference in time of exceution with my selection.
I will try it on a larger selection to see if there wil be a difference.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Thanks for the update.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now