Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Value at Position-Oriented  SQL Server 2005  While Loop

Posted on 2006-07-24
Medium Priority
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
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
  • 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 1200 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

704 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