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

Value at Position-Oriented SQL Server 2005 While Loop


Hi there!

When I code:

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

I get (correctly) the following:

    SgnQtyShares
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)
   RETURNS   * SOMETHING *
AS
BEGIN

DECLARE @RowCounter int
SET @RowCounter  = 0

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

 SELECT SgnQtyShares FROM dbo.EqtyTransactions  Where (StockID = 48) AND (SgnQtyShares < 0)
 AND  *** HERE IS WHERE I HAVE TO PUT SOMETHING  ****
ORDER BY EqtyTransID

  * ETC *

  END
RETURN

END
-------------------------

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.

Thanks,
fskilnik.




0
fskilnik
Asked:
fskilnik
  • 4
  • 3
1 Solution
 
derobyCommented:
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' ?)
0
 
fskilnikAuthor Commented:
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
            Else
                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...)

0
 
derobyCommented:
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

OPEN outer_loop CURSOR LOCAL
 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
    BEGIN

        PRINT @c_SgnQtyShares

        IF 1 = 2
            SELECT @keep_looping_ind = 0
   
        FETCH NEXT FROM outer_loop INTO @c_SgnQtyShares
    END
CLOSE outer_loop
DEALLOCATE outer_loop
0
Technology Partners: 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!

 
fskilnikAuthor Commented:

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,
fskilnik.

0
 
derobyCommented:
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 =)
0
 
fskilnikAuthor Commented:
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,
Fábio.


P.S.: just for the record:  where you wrote  "OPEN outer_loop CURSOR LOCAL" you certainly meant
"DECLARE outer_loop CURSOR LOCAL"  ;)  !
0
 
derobyCommented:
Yep, seems I mistyped that part ... mea culpa
(I'm not not used to using cursors, avoiding them wherever possible =)

happy coding...
0

Featured Post

Independent Software Vendors: 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!

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