Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I need to translate the below qv into sql code. The idea behind is that there is a number of databases each including a given Table that I need to source from. Because the number of dbs is high and dynamic, I use a for...next loop and a table with all dbs names (dbo_Configuration.dbo.Bases) to query them. Now, I need to translate it into sql using "while", and, got stuck. Could you please help preparing it?
Bases: SELECT Base_Name AS Base FROM dbo_Configuration.dbo.Bases
LET NoOfBases = NoOfRows('Bases');
FOR i=0 TO $(NoOfBases)-1
LET B = peek('Base',$(i),'Bases');
LET C = 'dbo_'& peek('Base',$(i),'Bases')& '.dbo.Table
SELECT *
FROM $(C);
NEXT
BR,
Przemek
Think you should exchange the "$(i)" with "i" in your LET-statements, similarly in the FOR-statement you may simply refer to NoOfBases or replace straight with the formula.
HTH
Peter
Thank you, Peter. I replace FOR...NEXT loop with WHILE but the problem is with PEEK - as far as I know there's no such function in SQL except for TOP but this only lets choose the very top row(s) where as PEEK lets you choose any row.
Using TOP 1 you may create a script but you will have to delete the row after reading which I don't want to do and look for a better solution.
BR, Przemek