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?
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