Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

Use a variable for Database Name - USE @DatabaseName SELECT.......

Hi

I am trying to figure out of there is any way that the below can work at all? Basically, it will be used in a Stored Procedure withthe DatabaseName passed in as a parameter. I have 3 databases with the same structure and tables and I need to choose which one to select from. Obviously i could have 3 sections of code so....

If @DatabaseName = 'FirstDatabase'
BEGIN.....

But I don't want to do that because i'd like it to be dynamic, see the below code............


DECLARE @DatabaseName As varChar(50)
@DatabaseName = 'Test1'

USE @DatabaseName SELECT * FROM TestTable


Any help gratefully received.

Craig

0
CraigBroadman
Asked:
CraigBroadman
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
u need to use dynamic sql

DECLARE @DatabaseName As varChar(50)
SELECT @DatabaseName = 'urDatabase'

EXEC('USE '+@DatabaseName+ ' SELECT * FROM TestTable')
0
 
CraigBroadmanAuthor Commented:
Is there anyway without using dynamic SQL as this will not be compiled, hence slower and more insecure?

Thanks
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
there are no otherways
0
 
CraigBroadmanAuthor Commented:
Don't think I am going to use that because it is too insecure!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
sometimes it is very difficult to find an alternate way..
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now