Is there a way to get the column value based on the row number?
I have a table function [dbo].GetValues()
in my SQL Server database that is written in C #. This function returns a table of integers within the text specified in the arguments. For example, it [dbo].GetValues('232dasdg34vb3')
will return the following table:
| Ints |
| ---- |
| 232 |
| 34 |
| 3 |
Now I am trying to use this result table in a loop WHILE
:
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 a way to access rows one by one in this loop using some index or row number?
Also note that I have no access to the source code GetValues()
.
UPDATE (Actual problem)
There are three tables in the A1, A2 and A3 database. All of these tables have a column [ID] which is a foreign key to another table as follows:
[A1].[ID] is connected to [A2].[A1ID]
[A2].[ID] is connected to [A3].[A2ID]
The text passed as an argument to the function contains integers, which are the [ID] s of table A3. Now I want the rows from A1 and A2 to use the [ID] of A3.
Now I have all the options that you people have suggested, including cursors and joins. But which one is more optimized for this situation and how to do it?
source to share
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
<s> You can use 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 simply join the result of the function call of the table function:
select * from SomeTable st
join [dbo].GetValues('232dasdg34vb3') ft on st.SomeID = ft.Ints
source to share
If you just need to select some records, a simple selection 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
)
source to share