Is there any way to get column value on the basis of row number?

Question

I have a table valued function [dbo].GetValues() in my SQL Server database which is written in C#. This function returns a table of integers inside the text specified in the arguments. For example, [dbo].GetValues('232dasdg34vb3') will return following table:

| Ints |
| ---- |
| 232  |
| 34   |
| 3    |

Now I am trying to use this resultant table in a WHILE loop:

DECLARE @IntCount int = (SELECT COUNT(*) FROM [dbo].GetValues('232dasdg34vb3'))
WHILE(@IntCount > 0)
BEGIN
    -- What to do here??
    SET @IntCount = @IntCount - 1
END

So, is there any way I can access the rows one by one in this while loop using some index or row number?

Also, please note that I don't have access to the source code of GetValues().

UPDATE (Actual Problem)

There are three tables in database A1, A2 and A3. All of these tables has a column [ID] that is foreign key to other table in following way:

[A1].[ID] is connected to [A2].[A1ID]
[A2].[ID] is connected to [A3].[A2ID]

The text passed as argument to the function contains integers that are the [ID]s of A3 table. Now, I want the rows from A1 and A2 table using the [ID] of A3 table.

I now have all the options suggested by you people including cursors and joins. But which one is more optimized for this situation and how to do it?

Do you really need a loop here? What are you doing as the body of the loop? Looping in sql is horribly inefficient and can usually be avoided. — Sean Lange, Jun 26 '15 at 14:51
@SeanLange Actually these integers are IDs of some other table rows. I want to fetch each of them one by one and process them. — Aishwarya Shiva, Jun 26 '15 at 14:56
@Aishwarya, In that case you *don't* need a loop. You should just need to join the results of GetValues() to the other tables. — nvogel, Jun 26 '15 at 14:59
Right but do you need to process them RBAR (row by agonizing row)? — Sean Lange, Jun 26 '15 at 14:59
@AishwaryaShiva: you can `CROSS APPLY` with the table valued function. — Tim Schmelter, Jun 26 '15 at 15:00
Similar to what @TimSchmelter said, you can use the function like a table and `INNER JOIN` the results to whatever other table(s) you need to. Look at examples here for [Table-Valued Functions and joining](http://stackoverflow.com/questions/23402316/inner-join-with-table-valued-function-not-working). And like others said, PLEASE don't use loops and cursors! — SlimsGhost, Jun 26 '15 at 15:04
Everyone please check my update. I explained the actual situation that I am facing. — Aishwarya Shiva, Jun 26 '15 at 15:18
It still is not clear at all what you are doing in the body of the loop. If you can explain that we can quite probably remove the loop entirely. — Sean Lange, Jun 26 '15 at 15:26
So, do you know from the list of ID's returned by the function, which ones are for table A1 vs A2? If so, then use whatever logic is required, and perform two `JOIN` operations for the two tables. If you don't know which ones are A1 ID's vs A2 ID's, of if it does not matter, then still do two `JOIN` operations, using the full set of returned ID's (as opposed to using "A1 subset" and "A2 subset" of the returned values). Same overall logic regarding Table-Valued Functions applies. — SlimsGhost, Jun 26 '15 at 18:13

Giorgi Nakeuri · Accepted Answer · 2015-06-26T15:23:35.190

3

EDIT:

select * 
from A1
join A2 on [A1].[ID] = [A2].[A1ID]
join A3 on [A2].[ID] = [A3].[A2ID]
join [dbo].GetValues('232dasdg34vb3') V on A3.ID = v.Ints

~~You can use a cursor:~~

 DECLARE @i INT

 DECLARE cur CURSOR FAST_FORWARD READ_ONLY FOR
 SELECT Ints FROM [dbo].GetValues('232dasdg34vb3')

 OPEN cur

 FETCH NEXT FROM cur INTO @i

 WHILE @@FETCH_STATUS = 0
 BEGIN

 /* cursor logic -- @i will hold 232, then 34, then 3 */

 FETCH NEXT FROM cur INTO @i

 END

 CLOSE cur
 DEALLOCATE cur

If you have those IDs in another table you can just join on result of calling table valued function:

select * from SomeTable st
join [dbo].GetValues('232dasdg34vb3') ft on st.SomeID = ft.Ints

edited Jun 26 '15 at 15:23

answered Jun 26 '15 at 14:52

Giorgi Nakeuri

35,155
8
47
75

Won't this unnecessarily call the function for each fetch? – Kaf Jun 26 '15 at 14:58
Can you use `Select *` ? Shouldn't it be `Select ColName`? – Kaf Jun 26 '15 at 15:05
@Kaf, it returns one column called `Ints` as I guess so `*` will work. – Giorgi Nakeuri Jun 26 '15 at 15:06
I updated my question. Want to confirm which is more optimized solution. – Aishwarya Shiva Jun 26 '15 at 15:20
@GiorgiNakeuri Thanks. That solved the issue. I am still a newbie to joins so don't know about the possibilities. Challenging situations reveal their possibilities. :) – Aishwarya Shiva Jun 26 '15 at 15:40

Kaf · Answer 2 · 2015-06-26T15:28:56.040

2

If you just need to select some records, a simple select can do the job:

DECLARE @Value VARCHAR(Max) = '232dasdg34vb3'

SELECT A1.Id, A2.Id
FROM A1 
     JOIN A2 ON A1.Id = A2.A1Id
     JOIN A3 ON A2.Id = A3.A2Id
WHERE EXISTS (
              SELECT 1 
              FROM [dbo].GetValues( @Value ) x 
              WHERE x.Ints = A3.Id
             )

edited Jun 26 '15 at 15:28

answered Jun 26 '15 at 14:50

Kaf

33,101
7
58
78

Just curious, what is `@CurrentRecordId`? The cursor option works, but I haven't seen this before. Hopefully though, the OP can do this without a loop or a cursor. – sgeddes Jun 26 '15 at 14:55
It is a user defined variable, I've updated my answer. – Kaf Jun 26 '15 at 15:00
I updated my question. Want to confirm which is more optimized solution. – Aishwarya Shiva Jun 26 '15 at 15:20
I've updated my answer. All three queries are equally good. Main thing is to avoid the loop or cursor. – Kaf Jun 26 '15 at 15:31

Tim Schmelter · Answer 3 · 2015-06-26T15:10:57.280

1

Don't use loops or cursors but set based approaches like:

SELECT x.Ints, ot.ID, ot.*
FROM OtherTable ot 
WHERE ot.ID IN (SELECT x.Ints FROM [dbo].GetValues('232dasdg34vb3'))

edited Jun 26 '15 at 15:10

answered Jun 26 '15 at 15:03

Tim Schmelter

450,073
74
686
939

Can you please check my update and tell me which way is more optimized according to my situation? – Aishwarya Shiva Jun 26 '15 at 15:23

Is there any way to get column value on the basis of row number?

3 Answers3