Value at Position-Oriented  SQL Server 2005  While Loop

Posted on 2006-07-24
Last Modified: 2012-05-05

Hi there!

When I code:

SELECT SgnQtyShares FROM dbo.EqtyTransactions
    Where (StockID = 48) AND (SgnQtyShares < 0)

I get (correctly) the following:

1    -1000
2    -1000
3    -750
4   -1000

And I want to put this SQL inside a "While" loop with the following additional restriction: at each loop, I have to get the "next" SgnQtyShares field value, from up to bottom.

In other words, I want to implement something like:

CREATE FUNCTION dbo.StockFifoNegMatrix(@StockID int)

DECLARE @RowCounter int
SET @RowCounter  = 0

WHILE @RowCounter < = ISNULL(dbo.NbPositSharesPerStock(StockID), 0) - 1
    aux_d =

 SELECT SgnQtyShares FROM dbo.EqtyTransactions  Where (StockID = 48) AND (SgnQtyShares < 0)

  * ETC *



Where the "something"  is related to the @RowCounter , of course.

I mean, first time at loop, please get the -1000 value, second time, again -1000, third time, -750 etc...

Please show me what should I put in the blank space.


Question by:fskilnik
  • 4
  • 3
LVL 11

Expert Comment

ID: 17167912
I think that you're mixing up things

For starters, as far as I know, a function cannot return multiple recordsets, even when you define them as returning n*SOMETHING*  =)

However, you could do this in a stored procedure.

From what you tell, I understand that you want to return multiple recordsets, each one returning 1 row (value) at a time ??

(if I may ask, how do you plan to use this in the 'next step' ?)

Author Comment

ID: 17168074
Hi, deroby!

Thanks for the quick reply.

>> For starters, as far as I know, a function cannot return multiple recordsets

Ok. I am sorry not to explicit that. This is an user defined function that returns a table... and the "extract" I showed is only a small part of it.

>> However, you could do this in a stored procedure.

I know. But I guess my approach would be better for what I have in mind, in the sense that the stored procedure would need a "EXEC" to trigger it and I want to avoid this if possible. (through Views "triggering" some functions, those functions  call this function you are helping me with...)

>> From what you tell, I understand that you want to return multiple recordsets, each one returning 1 row (value) at a time ??

No. It´s harder than that... and there will be much more lines in the * ETC *   :)

If you want to know exactly what I have in mind, please have a look at this algorithm:

 For i = 0 To NumberSharesUsed - 1
        aux_d = aux_a(i)                                 << -------------------------------------------------------
        For j = 0 To NumberSolicNeg - 1
            If NegStockID(j) <> StockID(i) Then Exit For
            If aux_d >= Abs(aux_b(j)) Then
                aux_c(i, j) = Abs(aux_b(j))
                aux_d = aux_d - aux_c(i, j)
                aux_b(j) = 0
                aux_c(i, j) = aux_d
                aux_b(j) = aux_b(j) + aux_d
                aux_d = 0
            End If
    Next j, i

You are helping me in the mentioned row above!   (Now variables are different. I put here just for your curiosity...)

LVL 11

Accepted Solution

deroby earned 300 total points
ID: 17168511
somehow I was assuming this would be conversion from language to another.

Please keep in mind that SQL does NOT like to be handled on 'row-by-row' basis, but rather prefers 'bulk' operations.

Anyway, if you want to stick to the orignal logic you probably want to use a cursor (which is not adviced because of reason above, but as an exception I'll give you the syntax anyway =)

I guess this should get you started for the outer loop, to break out of the loop I've added a bit-variable that you can set to 0 somewhere inside the loop (here WHERE 1 = 2, so in reality it will never get there off course... or at least, so I hope =)

DECLARE @c_SgnQtyShares int
DECLARE @keep_looping_ind bit

SELECT @keep_looping_ind = 1

 FOR SELECT SgnQtyShares
       FROM dbo.EqtyTransactions
      WHERE (StockID = 48)
        AND (SgnQtyShares < 0)
      ORDER BY EqtyTransID
OPEN outer_loop
FETCH NEXT FROM outer_loop INTO @c_SgnQtyShares
WHILE @@FETCH_STATUS = 0 AND @keep_looping = 1

        PRINT @c_SgnQtyShares

        IF 1 = 2
            SELECT @keep_looping_ind = 0
        FETCH NEXT FROM outer_loop INTO @c_SgnQtyShares
CLOSE outer_loop
DEALLOCATE outer_loop
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Author Comment

ID: 17168656

Great, deroby. That´s the kind of "structure" I had in mind.

>> Please keep in mind that SQL does NOT like to be handled on 'row-by-row' basis, but rather prefers 'bulk' operations.

I see. But this is a FIFO ("first in, first out") algorithm, therefore "sequential" by its own nature...

Please consider already the points given to you but wait a bit longer (say 2 more days, at most) for them because I may have a specific doubt in implementing your idea. Then I will ask and, of course, if I give you more "trouble" I will increase the points before giving them all to you, ok?!

Thanks a lot,

LVL 11

Expert Comment

ID: 17168686
No problem, I tend to be on EE whenever I have some "long-running" tests going on, so let's hope that the boss has more of them scheduled the coming days =)

Author Comment

ID: 17169851
I´m back, deroby.

Thanks a lot. It worked as expected (thanks) !   I will increase to 300 points because the "cursor possibilities" are exactly what I needed!

Best regards,

P.S.: just for the record:  where you wrote  "OPEN outer_loop CURSOR LOCAL" you certainly meant
"DECLARE outer_loop CURSOR LOCAL"  ;)  !
LVL 11

Expert Comment

ID: 17174178
Yep, seems I mistyped that part ... mea culpa
(I'm not not used to using cursors, avoiding them wherever possible =)

happy coding...

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

685 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