0

I have a requirement where I need to select data from database DB1 and insert into database DB2. 

update [Server1\SQLEXPRESS].[DB1].dbo.table1
set CName = (select CName 
             from [Server2\SQLEXPRESS].[DB2].dbo.table1   
             where CID = 3)

So above script is working fine.

Now I want to pass 

[Server1\SQLEXPRESS].[DB1]
[Server2\SQLEXPRESS].[DB2]

as parameters, because the server name and database can be different in real time environment.

Can you tell me how to achieve below goal?

create procedure
    @CID numeric,
    @ServerName1 serverDataType,
    @ServerName2 serverDataType,
    @DBName1 dbDataType,
    @DbName2 dbDataType
as 
    update @ServerName1.@DBName1.dbo.table1
    set CName = (select CName 
                 from @ServerName2.@DBName2.dbo.table1   
                 where CID = @CID)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
yogeshkmrsoni
  • 315
  • 7
  • 21
  • 3
    This can be easily solved using dynamic sql, However I would suggest to use it very carefully since it's prone to sql injection attacks. Perhaps someone can come up with a better idea. – Zohar Peled May 14 '15 at 06:54
  • Even with dynamic sql, this will only work then you have linked servers for those servers configured appropriately. If this is the only sql in your procedure which uses the Server and database, you can create a `sqlconnection` to `Db2` in `ServerName2`, get the `CName` using a `SELECT` and pass it to the procedure for update on a connection with `ServerName1`. – ughai May 14 '15 at 07:00
  • It's not a good idea to pass database name as a parameter to the operative piece of code that actually writes to the database. A much better solution would be to parametrize the database name in database-access encapsulation (layer). Then you can write a helper method which firstly sets the database to write to, and then executes the update. – OzrenTkalcecKrznaric May 14 '15 at 07:00

1 Answers1

0

Try this something in this fashion:

DECLARE @ServerName1 varchar(max) = 'MyServer'
DECLARE @DB1 varchar(max) = 'MyDB'

EXEC('update   ' + @ServerName1 +  '.' + DB1  + '.dbo.table1
      set CName= (select  CName from ' + @ServerName1 +  '.' + DB1  +           
                  '.dbo.table1'   where   CID ='+ @CID)
Marko Juvančič
  • 5,792
  • 1
  • 25
  • 41
  • As I wrote to the OP, this is prone to sql injection attacks. I would avoid using this kind of solution for the price of having to write and maintain even 50 different sql queries. – Zohar Peled May 14 '15 at 07:07