Solved

SQL cursor with storedProcedure

Posted on 2015-02-05
18
129 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 50

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 34

Accepted Solution

by:
ste5an 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 34

Expert Comment

by:ste5an
ID: 40590630
btw, there is no need for a cursor. Just use a table value parameter and to it set-based.
0
 

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 50

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 50

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 50

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 50

Expert Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

751 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