How to dynamically qualify the database name in a query

• bob mazzo

  SSCommitted

  Points: 1684

  More actions

  September 21, 2006 at 11:24 am

  #114586

  When we access a different database from a stored procedure or trigger, we need to qualify the table name with the database name. For example, to access database TestDB I would do the following :

     Update TestDB..Orders set amount = @amount where ordernum = @ordernum

  I've used dynamic Sql to generate certain select and update commands; however, I can't figure out how to dynamically include the database qualifier.

  For example, in my proc I have this code to build an Update cmd:

    if isNull(@pSqlTable, '') <> ''

       SET @SqlStr = 'Update ' + @pSqlTable + ' SET ' + @pSqlSet

    if isNull(@pSqlWhere, '') <> ''

       SET @SqlStr = @SqlStr + ' WHERE ' + @pSqlWhere

    EXEC sp_executeSql @SqlStr

   

   I've tried something like this to soft-code the database but it doesn't work (I forget the error):

   SET @pDBName = 'TestDB'

   SET @SqlStr = 'Update ' + @pDBName + '..' + @pSqlTable + ' SET ' + @pSqlSet

  CAn anyone give me some advice on this ?

  Thank you,

  Bob

• Bhushan

  Default port

  Points: 1427

  More actions

  September 21, 2006 at 11:58 am

  #661827

  If the owner is 'dbo', your statement should work otherwise include (replace .. with .<>.) the schema name

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  September 25, 2006 at 3:53 am

  #662154

  If you need to assign a variable to the database name you might just as well hardcode it avoid using dynamic sql.

  select * from pubs.dbo.testrs

  or

  declare @string varchar(250),@db varchar(25)

  set @db='pubs'

  set @string= 'select * from '+@db+'.dbo.testRS'

  exec(@string)

   

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/