Using DBname as a parameter in a SP

• ouertani neked

  SSC-Addicted

  Points: 406

  More actions

  June 8, 2004 at 11:14 am

  #161600

  Hi everybody,

  is it possible to call a SP with the Database(s) to useas a parameter:

  so far I can use the Exec instruction with a concatenated string:

  exemple: Set @sql =    'SELECT * FROM '+ @DBname' +  TableName   then

  Exec (@SQL)

  The problem is when I want to use the dbname parameter to create a cursor or to affect the result of a query into a variable. Is this possible?

  thanks

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  June 9, 2004 at 12:08 am

  #509585

  I don't quite understand the question.

  Could you give an example on what you're trying to do, and what the results are when trying to do so?

  /Kenneth

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  June 9, 2004 at 12:44 am

  #509589

  Oh man, once again I recommend reading this this

  Dynamic SQL itself is mostly a sub-optimal solution, but dynamic sql in a cursor seems to be even worse.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• G.R.Prithiviraj Kulasingham

  SSCarpal Tunnel

  Points: 4105

  More actions

  June 9, 2004 at 4:06 am

  #509602

  1.  it is poosible to have a dynamic sql statement to execute. 

  2.  remember to use 3 part name for tables  (DBNAME+'.'+SCHEMA+'.'TABLENAME)

  3.  try to avoid the cursor, but if you wan to run in any order no other option. Further If you dont have much databases, that wont be a big issue

   

   

   

  Cheers,
  Prithiviraj Kulasingham

  http://preethiviraj.blogspot.com/