Local variable for DB location in select statement

[Craig]

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.

[C. E. Buttles]

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.

[C. E. Buttles]

"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.
>

[Dale]

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.
>