Dynamic sql and looping - changing variable name?

catbeasy · November 28, 2018, 4:17pm

Hello All, when I write VBA, I can declare an array as a variable as varName(2) and then reference that variable in a loop with the variable varName plus a counter, something like:

dim varName(2) as variant
dim slqcmd as string
dim i as integer

do until i = 3
set i = 1
sqlcmd = "select * from " & varName(i) 'here is where the variable gets concatenated with the loop counter
set i = i+1
loop

Can i do something similar in sql? Doing it in VBA requires declaring an array so that it recognizes the change in the variable (i.e. adding the "i" to varName to make it varName1, then after 1st loop, varName2 etc..

The basics of the sql I want are as below, so like the above VBA code above, I'm trying to concatenate the "Tbl" with @cnt to get the first table so the variable name would have to change to @tbl1, then @tbl2 etc..of course, this doesn't work since @tbl isn't a recognized variable - trying to know how to concatenate "Tbl" with @cnt in some way prior to the engine knowing its not a variable - i also tried a bit of dynamic sql - but that doesn't seem to work as it then views the variable as text..

Note, I do know how to create a table for this and then then cursor through the recordset, but want to know if this can be done this 'array' way way for conceptual reasons..(if, however, it can't be done the way I'm trying, i suppose i'll have to resort to creating the table..)

Thanks for any help..

DECLARE
@Tbl1 varchar(200)
,@Tbl2 varchar(200)
,@cnt int

set @Tbl1 = 'Dim_Proj'
set @Tbl2 = 'Dim_Meas'

set @cnt = 1
WHILE @cnt < 3

BEGIN
PRINT 'SELECT * FROM ' + @tbl + CAST(@cnt AS VARCHAR(10))
set @cnt = @cnt + 1
END

harishgg1 · November 28, 2018, 5:12pm

hi

i know you are talking about VBA

i have a different way
using recursive CTE and tally table

using SQL with recursive CTE and tally table

;WITH tally_recursive_cte 
     AS (SELECT N=1 
         UNION ALL 
         SELECT n + 1 
         FROM   tally_recursive_cte 
         WHERE  n + 1 <= 10) 
SELECT 'select * from table' + Cast(n AS VARCHAR) 
FROM   tally_recursive_cte 

go

Result

catbeasy · November 28, 2018, 5:23pm

Thanks for that harish, that is good to know; however, as i mentioned, not looking for VBA, i just used that as an analogy to what i want to do in dynamic sql. So, want to know if it is possible to do this via dynamic sql and if so, how to do it..mainly looking to see if this is valid conceptually, not looking for another way to do this (though I do very much appreciate your suggestion!)..

Thanks..

harishgg1 · November 28, 2018, 6:04pm

hi

it is very easy to do in dynamic SQL

i am experienced in doing dynamic SQL

Please..
let me try to do something here

dynamic sql

DECLARE @i INT = 1 
DECLARE @sql VARCHAR(100) = '' 

WHILE @i < 10 
  BEGIN 
      SET @sql = ' select * from table ' 
                 + Cast(@i AS VARCHAR) 

      PRINT @sql 

      SET @i = @i + 1 
  END

Result

catbeasy · November 29, 2018, 7:37pm

Hi Harish

Yeah, so i am not trying to add a number to the end of a string (text) as you showed in your example. To use a bit from your example, what I am trying to is iterate through a bunch of tables by changing, not the string, but the variable, so using your example, it would look something like:

declare
@var1 = 'fact_prov'
,@var2 = 'fact_meas'
,@i = int
,@sql varchar(200)

while @i < 3
begin
Set @sql = 'select * from var' + (i)
print @sql
end

So, in this example, the code will iterate through var1 and then var2 where @i is being concatenated to the end of @var to first make @var1, then on the 2nd pass, @var2. So the results should print each table, fact_prov and fact_meas..

However, when i try this, it doesn't work, as it appears i cannot concatenate a string (var) and a variable (@i) together to make the value of the variable var1 (and then on the next loop, var2 etc..)

Thanks..

EDIT: Note, this isn't obviously correct sql (because it doesn't work - which is why i'm inquiring!), these are the closest examples i could come to trying to show what i'm trying to do..I had mentioned VBA before because, through the use of declaring an array, you can take a variable and add a number to it so it iterates through previously defined array variables. Wondering if there is anything similar in SQL Server

btrimpop · November 29, 2018, 9:42pm

Are you trying to actually select data from the tables or just print out the select string?
Is something like this what you're looking for? Since you're hardcoding your var1 and 2 values just skip that and build a case statement that passes the table name instead.

declare @i Int = 1,
@sql varchar(200),
@table varchar(20)

while @i < 3
begin
SET @table = case when @i = 1 Then 'fact_prov'
when @i = 2 Then 'fact_meas'
end
SET @sql = 'select * from ' + @table
print @sql
SET @i= @i + 1
end

jeffw8713 · November 29, 2018, 11:26pm

This process is not really something that you would even want to perform in SQL. It would probably help if you could identify why you want to be able to do something like this...

Now - since SQL's array is a table you can utilize a table for this type of process:

Declare @array Table (ArrayIndex int identity(1,1) Primary Key, ArrayValue varchar(50));

 Insert Into @array Values ('Dim_Proj'), ('Dim_Meas');

 Select *
   From @array;

Declare @increment int = 0
      , @tableName varchar(50) = ''
      , @maxCount int = (Select max(ArrayIndex) From @array);

  While @increment < @maxCount
  Begin
    Set @increment += 1;
    Set @tableName = (Select ArrayValue From @array Where ArrayIndex = @increment); 
  Print concat('Select * From ', @tableName, ';');        
    End

With that said - I would include a step that validates the table name is a valid table in the database. That can be done by joining to sys.tables:

    Set @tableName = (Select a.ArrayValue 
                        From @array             a
                       Inner Join sys.tables    t On t.name = a.ArrayValue  
                       Where a.ArrayIndex = @increment); 

     If @tableName Is Not Null
  Print concat('Select * From ', @tableName, ';');

This would help eliminate any potential injection issues with your dynamic SQL...depending on how you are actually going to utilize this type of process.

catbeasy · November 29, 2018, 11:43pm

Hi btrim, your solution is what i was looking for: a way to do this without as much code and when there aren't very many table names to go into the case statement (there are actually 10 that i will use). It is elegant and simple, though may not include protections against injection.

That's something i will have to look into - i don't know if here at work that's something that even exists or to what level i should worry about it if it does. At the least i do cop to the fact that its something i should know about and consider as a 'programmer'. But first things first..concepts! Thanks for your valuable assist..!

catbeasy · November 29, 2018, 11:52pm

Hi Jeff

Thanks for posting that. As I explained in my reply to btrim, injection is something i will check into.

And as i explained in my earlier post, this was more for conceptual benefit. I come from a VBA background and so being relatively new to T-SQL, am trying to feel my way around using VBA as a guidepost and analogy. I would imagine that at some point, as I gather more and more info, i will start wearing T-SQL goggles in place of VBA eyewear and so start to think more in SQL terms than VBA..new concepts/ideas have already opened just from this post!

btw, your solution is cool, its sort of what i already knew to do, albeit not exactly with the same syntax - i.e. i knew enough to build a table and insert the records i want into that table and then reference that table for value-injection into my DML SQL..However, wanted to see about different ways to do this as i figured this would teach me more about how variables are used syntactically (different from VBA) and from previous experience, that also opens up other stuff down the road that might be unrelated to this specific task i have..

I had also known to put a check on making sure that the table (and also the record id that the user will supply!) exists - these are things i would do in VBA as well - i believe they are general concepts that belong to any code you write of this nature..

Again, I do appreciate the code and the explanations. The table solution i had did not include a specific Array variable as you had and also your table check is different syntax that i used, so that is valuable as well!

Cheers!

btrimpop · November 30, 2018, 2:47pm

Hi,

Yes, the CASE solution is for a small set of table names. To carry Jeff's solution a step further if the table names you need all share a commonality in naming such as they all start with 'fact_' you could populate the array table directly from sys.tables. Something like:

Insert Into @array(ArrayValue) Select t.name from sys.tables t where t.name like 'fact_%'

harishgg1 · November 30, 2018, 3:23pm

hi catbeasy

This is a different solution

I am doing this
because you are trying to learn TSQL
I think

SQL

SELECT * 
FROM   (SELECT 'fact_prov' tablename 
        UNION ALL 
        SELECT 'fact_meas') a 
       CROSS JOIN (SELECT TOP 10 N=number 
                   FROM   master..spt_values 
                   WHERE  type = 'P') b

Result

catbeasy · November 30, 2018, 5:06pm

thanks btrim, unfortunately, they don't all start with any commonalities that i could use..but yeah, i see that is potentially a good solution..thanks!

catbeasy · November 30, 2018, 5:06pm

Thanks Harish..nice work!