Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    74

    Unanswered: Database Name as a Parameter

    Hello folks,

    I am in the process of writing a stored proc that selects data from a set of tables. Since this procedure needs to run in multiple databases, I wanted to store database names in a table and have the proc. retrieve the names dynamically.

    In short, I want to execute the following stt:
    select 'Conversion 1,
    'Query 1',
    (select count(*) from @testDB.student)

    (in this case @testDB could be "parul1.dbo" or "parul2.dbo" etc.

    How can this functionality be achieved through dynamic SQL?

    Thanks so much!
    -Parul

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    Code:
    declare @sql nvarchar(1000),@testDB varchar(100)
    set @testDB='parul1'
    set @sql='select ''Conversion 1'',''Query 1'',count(*) from '+@testDB+'.dbo.student'
     
    print @sql
     
    exec sp_executesql @sql
    Last edited by mallier; 05-18-06 at 11:19.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Oct 2002
    Posts
    74
    Thanks, that worked!
    I have another question - how can i put the results from "EXEC sp_executesql @SQLSTRING" into a temp table?

    How can I get this to work?
    Insert #temp1
    select @stage,
    'Query 1',
    @sqlStringQuery1,
    EXEC sp_executesql @sqlStringQuery1

    Thanks so much!

    -Parul

  4. #4
    Join Date
    Jul 2004
    Posts
    3
    create a procedure which takes in DB name as parameter...do the select and the insert inside the procedure.

  5. #5
    Join Date
    Oct 2002
    Posts
    74
    Thanks, do you have sample code?

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Insert JUST the EXEC into a temp table first. Then, insert into a second temp table the results of that table joined to whatever else you need to insert.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •