Local variable for DB location in select statement


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Local variable for DB location in select statement

  1. #1
    Craig Guest

    Local variable for DB location in select statement


    Hi

    I'm writing a SQL script that reads a table into a cursor and then imports
    the data into various tables in another database. I don't know the name
    of the source database, so I want to declare the database name as a local
    variable. This will allow the end user (the DBA running the script) to put
    the database name in when they need to run it. I tried the following:

    declare @DBName varchar(40)
    set @DBName = 'TestDB'
    declare DB_cursor cursor for
    select * from @DBName.dbo.ImportData
    open DB_cursor

    I've found that you can't use a local variable in the database location for
    a select statement. Is there some way of achieving the result I'm after?

    Thanks.

  2. #2
    C. E. Buttles Guest

    Re: Local variable for DB location in select statement

    Put your select statement in a variable (like this: Select @sqlstmnt="SELECT
    * FROM "+@dbname+" (add other stuff)"), then
    EXEC @sqpstmnt
    You may need to USE the db first (SELECT @sql1="USE "+@dbname) followed by
    EXEC @sql1. Then use the previous statements.

    "Craig" <cjewiss@hotmail.com> wrote in message
    news:392dca2a$1@news.devx.com...
    >
    > Hi
    >
    > I'm writing a SQL script that reads a table into a cursor and then imports
    > the data into various tables in another database. I don't know the name
    > of the source database, so I want to declare the database name as a local
    > variable. This will allow the end user (the DBA running the script) to

    put
    > the database name in when they need to run it. I tried the following:
    >
    > declare @DBName varchar(40)
    > set @DBName = 'TestDB'
    > declare DB_cursor cursor for
    > select * from @DBName.dbo.ImportData
    > open DB_cursor
    >
    > I've found that you can't use a local variable in the database location

    for
    > a select statement. Is there some way of achieving the result I'm after?
    >
    > Thanks.



  3. #3
    C. E. Buttles Guest

    Re: Local variable for DB location in select statement


    "C. E. Buttles" <cebuttle@sprintsvc.net> wrote in message
    news:3933b402$1@news.devx.com...
    > Put your select statement in a variable (like this: Select

    @sqlstmnt="SELECT
    > * FROM "+@dbname+" (add other stuff)"), then
    > EXEC @sqpstmnt


    This was a typo. It should read: EXEC @sqlstmnt

    Sorry 'bout that.

    to USE the db first (SELECT @sql1="USE "+@dbname) followed by
    > EXEC @sql1. Then use the previous statements.
    >
    > "Craig" <cjewiss@hotmail.com> wrote in message
    > news:392dca2a$1@news.devx.com...
    > >
    > > Hi
    > >
    > > I'm writing a SQL script that reads a table into a cursor and then

    imports
    > > the data into various tables in another database. I don't know the name
    > > of the source database, so I want to declare the database name as a

    local
    > > variable. This will allow the end user (the DBA running the script) to

    > put
    > > the database name in when they need to run it. I tried the following:
    > >
    > > declare @DBName varchar(40)
    > > set @DBName = 'TestDB'
    > > declare DB_cursor cursor for
    > > select * from @DBName.dbo.ImportData
    > > open DB_cursor
    > >
    > > I've found that you can't use a local variable in the database location

    > for
    > > a select statement. Is there some way of achieving the result I'm

    after?
    > >
    > > Thanks.

    >



  4. #4
    Dale Guest

    Re: Local variable for DB location in select statement


    You are actually not allowed to have USE <database> statements in stored procedures.
    But then you don't need them, Craig, if you are using the fully qualified
    name for the table you want to query i.e. server.db.owner.table, like you
    have in your code, and then using dynamic sql as C.E.Buttles suggests.


    "C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
    >Put your select statement in a variable (like this: Select @sqlstmnt="SELECT
    >* FROM "+@dbname+" (add other stuff)"), then
    >EXEC @sqpstmnt
    >You may need to USE the db first (SELECT @sql1="USE "+@dbname) followed

    by
    >EXEC @sql1. Then use the previous statements.
    >
    >"Craig" <cjewiss@hotmail.com> wrote in message
    >news:392dca2a$1@news.devx.com...
    >>
    >> Hi
    >>
    >> I'm writing a SQL script that reads a table into a cursor and then imports
    >> the data into various tables in another database. I don't know the name
    >> of the source database, so I want to declare the database name as a local
    >> variable. This will allow the end user (the DBA running the script) to

    >put
    >> the database name in when they need to run it. I tried the following:
    >>
    >> declare @DBName varchar(40)
    >> set @DBName = 'TestDB'
    >> declare DB_cursor cursor for
    >> select * from @DBName.dbo.ImportData
    >> open DB_cursor
    >>
    >> I've found that you can't use a local variable in the database location

    >for
    >> a select statement. Is there some way of achieving the result I'm after?
    >>
    >> Thanks.

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center