ServerName/Database Name Parameterization

ServerName/Database Name Parameterization

• A_Y

  Also, check out the link below for a discussion on pros & cons of dynamic SQL.

  http://www.sommarskog.se/dynamic_sql.html

  
  
  

• Hatem Rabie

  Nope, not in SQL. And it is not really a good idea, even using dynamic SQL. If you could do this, the plan that could get saved would be garbage. By using only fully qualified objects, the procedures can use pre-compiled plans for subsequent executions.

  The best thing to do is have one procedure for one action. Now, a really neat trick for doing this is to use 2005's SQLCMD and it's tags. I use it all of the time when I cannot be sure of the database name.

  :servername bobs
  :dbname youruncle

  use $(dbname)
  go

  create procedure test
  as
  select * from $(servername).somename.somename.somename

  GO

  Etc. Then you can just vary the parameters. If you want to do it in many places, you can use DOS variables to vary the values programatically for an installation to a location that might vary.

  Look up SQLCMD in the books online, it has lots of very cool features for doing this.

  
  
  
• ServerName/Database Name Parameterization