• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 123
  • Last Modified:

stored procedure that iterates through records and execute another process

Hi there,
Currently i have a select statement that returns account information and devices. something like this:
SELECT * FROM ACCOUNTS WHERE STATUS='ACTIVE'

Open in new window

Ok, fine that returns all of my active accounts. this result set gives me Device, PortNumber, Account, Units, XrefID now i need to call a second process that pass the Device and PortNumber to get me the final result. so the call looks like this:
EXEC CalcConsumption [DEVICEFROMPREVIOUSQUERY], [PORTNUMBERFROMPREVIOUSQUERY, '12-22-2014','02-15-2015'

Open in new window

whats the best practice to do something like this and how do i go about it?
Thanks!
0
COHFL
Asked:
COHFL
  • 5
  • 4
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use a cursor:
DECLARE @Device VARCHAR(50)
DECLARE @PortNumber INT

DECLARE CursorName CURSOR FOR  
SELECT DEVICE, PORTNUMBER
FROM ACCOUNTS 
WHERE STATUS='ACTIVE'

OPEN CursorName 
FETCH NEXT FROM CursorName INTO @Device, @PortNumber 

WHILE @@FETCH_STATUS = 0   
BEGIN   
       EXEC CalcConsumption  @Device, @PortNumber, '12-22-2014','02-15-2015'

       FETCH NEXT FROM CursorName INTO @Device, @PortNumber
END   

CLOSE CursorName   
DEALLOCATE CursorName

Open in new window

0
 
Scott PletcherSenior DBACommented:
If it's not a huge number of rows, say 20K or less,  you can use a cursor.  For counts above that, I'd suggest generating dynamic SQL with a string of EXECs in it.

Also, be sure to explicitly specify FAST_FORWARD and LOCAL on the cursor, for efficiency:

DECLARE cursor_devices FAST_FORWARD LOCAL CURSOR
FOR
...
0
 
COHFLAuthor Commented:
the first return set is about 40k records
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Scott PletcherSenior DBACommented:
Hmm, still borderline (20K is a very rough estimate :-) ); however, if you leave the cursor dynamic and updateable (i.e. don't specify fast_forward), then I would limit it to 20K.  Try the cursor and see how it performs.  If you think it's too slow, we can try another approach.
0
 
COHFLAuthor Commented:
Msg 153, Level 15, State 1, Procedure usp_..., Line 34
Invalid usage of the option local in the DECLARE CURSOR statement.
Msg 153, Level 15, State 1, Procedure usp_..., Line 34
Invalid usage of the option fast_forward in the DECLARE CURSOR statement.

i get this after i add
DECLARE Cons FAST_FORWARD LOCAL CURSOR FOR
0
 
Scott PletcherSenior DBACommented:
Oops, sorry, got the syntax wrong, CURSOR has to go first for those options:

DECLARE Cons CURSOR FAST_FORWARD LOCAL FOR
0
 
COHFLAuthor Commented:
Got it let me see if this gives me what i need and how long it will take
0
 
COHFLAuthor Commented:
Im getting this error: An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.
error.JPG
0
 
Scott PletcherSenior DBACommented:
Odd.  Hard to believe you're so short of RAM that a single cursor causes that.  Make sure you DEALLOCATE any previous cursor(s).
0
 
COHFLAuthor Commented:
I dont have any other cursor running.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You may working with a large amount of data so I'll recommend you to declare the cursor with the following options:
DECLARE Cons CURSOR FORWARD_ONLY READ_ONLY FOR
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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