Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2007
    Posts
    167

    Unanswered: Using a variable in the "Select * FROM mydbname" sql statement...

    Hello... I would like to use a variable in place of a literal string in my SELECT statement. But doesn't seem possible. For example...

    Instead of...

    select * from mydbname

    I would like to use...

    declare @mydbname

    select * from @mydbname

    Is this possible? If so, how? It seems that no matter how creative I get with building the string, it just won't work.

    What are your thoughts?

    Thank you.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Use dynamic sql e.g.
    declare @tname varchar(100)
    set @tname='sysobjects'
    exec ('select * from '+@tname)

  3. #3
    Join Date
    Mar 2007
    Posts
    167

    when creating a temp table, dynamic sql seemed to fail...

    Thank you for your help and recommendation. I tried dynamic sql, but seemed to fail. Maybe I'm overlooking something...

    I'm trying to create a procedure that will reside in the sybsystemprocs database, which will allow me to pass a parameter for the db name. I want to read the sysindexes table from each database by passing the database name as a parameter.

    For example...

    create procedure (@dbname char(50) = null)
    as
    begin
    declare @dbname varchar(100)
    set @dbname='mydb'
    exec ('select id,doampg,ioampg into #tmp from '+@dbname+'.dbo.sysindexes')

    select * from #tmp
    end
    go

    -------------------------------------------------------------

    I think that I just realized that this may not be possible. Is it possible to perform a select statement on another database table, from within another?

    Thank you.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You need to create the temp table before your exec statement

    declare @dbname varchar(100)
    set @dbname='mydb'
    select id,doampg,ioampg into #tmp from master.dbo.sysindexes where 1=2 -- create table
    exec ('insert into #tmp select id,doampg,ioampg from '+@dbname+'.dbo.sysindexes')

    select * from #tmp
    drop table #tmp

    PS. Make sure you validate parameters you use in dynamic sql else you open yourself to hacking via SQL Injection
    Last edited by pdreyer; 06-05-07 at 06:55. Reason: PS

  5. #5
    Join Date
    Mar 2007
    Posts
    167

    Thank you... :)

    Thanks for your help and great solution. Great idea! I will do as you recommended.

    Thank you.

Posting Permissions

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