How to pass in database name for DDL script

• tbf

  Old Hand

  Points: 347

  More actions

  September 5, 2006 at 11:12 am

  #114317

  Hopefully, I'm posting this question in the right discussion group...

  I need to write some DDL that creates some views. The SELECT defining some of the views needs to join on tables in a database other than the database where the view will live. The plan is to reference the "full path" of the table in the join clause... i.e. "configDb.dbo.tblClient".

  The DDL script that is creating these views may be invoked by a variety of different programs (such as sqlcmd, Ant, JDBC client, etc.) and it is assumed the invoking program will somehow pass-in the name of this database.

  Is there a generally accepted way to solve the problem of passing a parameter into DDL scripts, such as turning the script into a sproc or using an environment variable?

  TIA for your help... tom

• Gopi Muluka

  SSCarpal Tunnel

  Points: 4594

  More actions

  September 5, 2006 at 11:35 am

  #658616

  Try this

  Create

  Procedure CreateView(@dbname as sysname)

  as

  begin

  declare

  @sql nvarchar(4000)

  set

  @sql=N'Create View TestView as Select * from '+@dbname+'.dbo.ErrorLog'

  execute

  sp_executesql @sql

  end

• tbf

  Old Hand

  Points: 347

  More actions

  September 5, 2006 at 11:48 am

  #658621

  Thanks. That is the direction I was heading... good to have a little positive validation... tom