Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL cursor with storedProcedure

Posted on 2015-02-05
18
Medium Priority
?
131 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 52

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 35

Accepted Solution

by:
ste5an earned 2000 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 11

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 35

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 52

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 52

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 52

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 52

Expert Comment

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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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