CODEWITHSUNDEEP
c#sqlsql-serverrowstraversal
Aishwarya Shiva
Aishwarya Shiva

Reputation: 3406

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

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?

Upvotes: 3

Views: 216

Answers (3)

Kaf
Kaf

Reputation: 33819

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
             )

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460138

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'))

Upvotes: 1

Related Questions