CODEWITHSUNDEEP
sqlsql-server
Sanket J
Sanket J

Reputation: 187

I am trying to use table variable as a dbname to iterate through multiple databases

I am trying to execute the following piece of code:

use projects

set nocount on

CREATE TABLE #t (dbname VARCHAR(20),  col2 datetime)

declare @sql nvarchar(max)

declare @proc_sql nvarchar(max)

declare @table_car table (rowid int not null primary key identity(1,1), carrier varchar(500))

insert into @table_car (carrier)

select distinct t.db_name from table t with(nolock)   
              inner join table_t1  t1 with(nolock) on t.id = t1.id                          

declare @rowstoprocess int

declare @currentrow int

declare @dbname varchar(500)

set @rowstoprocess = @@rowcount

set @currentrow = 0

while @currentrow < @rowstoprocess

begin

    set @currentrow = @currentrow+1

    select @dbname = carrier
        from @table_car
        where rowid = @currentrow
         select @sql = '
        insert into #t (dbname, col2)
        SELECT DISTINCT db_name(), col_x from'+@dbname+'..table_xyz'
        set @proc_sql = replace(@sql,   @dbname, @dbname)
        exec (@proc_sql)

end

However, when I execute this, I am getting an error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

which I believe has nothing to do with Line 3 but the has a syntax error near the @dbname variable being used inside the while loop. Please help

Upvotes: 0

Views: 147

Answers (1)

db_brad
db_brad

Reputation: 923

I believe you are just missing a space between right after the from in "col_x from'+@db_name". Should be like this: 

SELECT DISTINCT db_name(), col_x from '+@dbname+'..table_xyz'
-------------------------------------^

One thing you can do is PRINT out your sql strings when debugging and then run them on their own. Easier to debug. So in this example, instead of EXEC (@proc_sql) do PRINT @proc_sql.

Upvotes: 3

Related Questions