0

I have 2 db on one server. I need to write procedure that does select basing on @db variable.

I know 2 possibilities for this:

  1. I declare @SQL nvarchar(max) and generating my query in plain text. Then i do exec @SQL. Bad variant imho.

  2. I do 2 similar queries and use if @db='' 1st query else 2nd query. Another bad variant because it is code duplicate.

Question is - is there any way to do like this or similar: select * from @db.dbo.table?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
StNickolas
  • 576
  • 7
  • 20
  • You might want to look at synonyms, but I don't know if you can create one mid-batch. http://msdn.microsoft.com/en-us/library/ms177544.aspx – ta.speot.is Mar 19 '12 at 03:56
  • Table names, including database, can not be specified with a variable without the use of dynamic SQL. The only other alternative is to prompt the user for the value, and generate a script with the value hardcoded based on user input. – OMG Ponies Mar 19 '12 at 04:02

1 Answers1

0

Using "exec @SQL" isn't evil. If it gets the job done and you're not exposing yourself to any security risks then it may be the best way to go. Another option would be to consider using a real programming language like c# (or whatever your preference is) since they are better equipped to handle these sort of dynamic requirements.

Brandon Moore
  • 8,590
  • 15
  • 65
  • 120