mysqlsqlopenqueryodbc-sql-server-driver

Dynamic sql query from MS SQL to MySQL


I have 2 dbs one on MS SQL and another on Mysql. I have written some sql scriots which fetch data from Mysql and insert into MS SQL. To do this I have set up device driver connection and linked server on MS SQL and these scripts are executed on MS SQL. Scripts are simple and have only select statements. Now I need to write dynamic scripts to fetch data from mysql based on some parameters. Below is sample query to get data from DB "stagedb_ie" where ie stands for Ireland.

select * from openquery(stagedb_ie, 'select * from stagedb_ie.aol_center')

There are other dbs in mysql with country code suffix to stagedb as name. Now I want to pass this country code as parameter to query and get data. e.g.

declare @stagedb_country varchar(20)
set @stagedb_country = 'stagedb_ie'
select * from openquery(@stagedb_country, 'select * from '+ @stagedb_country +'.aol_center')

But this query doesn't work. any suggestion?


Solution

  • declare @stagedb_country varchar(20),
       @SQL nvarchar(max);
    set @stagedb_country = 'stagedb_ie';
    set @SQL = 'select * from openquery(' + @stagedb_country
       + ', ''select * from ' + @stagedb_country + '.aol_center'')';
    exec (@SQL);
    

    Be aware that dynamic SQL in SPs executes with the permissions of the caller not the SP owner.

    Last, I question why you're using separate databases and tables for this. Can you not encode what you need in the data instead of putting the data in different containers?