Solved

SQL cursor with storedProcedure

Posted on 2015-02-05
18
124 Views
Last Modified: 2015-02-12
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
Comment
Question by:staleb
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40590588
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
 

Author Comment

by:staleb
ID: 40590595
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40590596
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
 

Author Comment

by:staleb
ID: 40590604
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40590614
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40590616
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
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
ID: 40590618
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
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40590625
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
 
LVL 32

Expert Comment

by:Stefan Hoffmann
ID: 40590630
btw, there is no need for a cursor. Just use a table value parameter and to it set-based.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:staleb
ID: 40590632
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40590635
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40590637
@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
 

Author Comment

by:staleb
ID: 40590695
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40590699
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
 

Author Comment

by:staleb
ID: 40590731
@Vitor, yes I will try Yours to
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40590733
Please do. I'm curious about the performance. Usually I avoid cursors :)
0
 

Author Comment

by:staleb
ID: 40605591
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40605597
Ok. Thanks for the update.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now