Solved

SQL cursor with storedProcedure

Posted on 2015-02-05
18
128 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 48

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 33

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 33

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 48

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 48

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 48

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 48

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL STANDARD CORE 8 40
SQL Query 2 34
Need return values from a stored procedure 8 23
SSRS: Why is Visual Studio stripping these properties? 2 23
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

820 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