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

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

809 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