How can you assign system objects to a variable?#$@!

[mivey4]

Hi.

I am having a problem trying to find the proper way to assign a system object (or rather in this case a system object name) to a variable to use in my T-SQL code.

I have to run a report across 20 or more databases and need to create a loop to run the same report execution on every database.

This is a snippet of the code I am trying to execute:
-- Create a temp table to store each database on the server in a table with a
-- corresponding incremental integer

create table #databases
(x int identity(1,1),dbase nvarchar(128))
go
insert into #databases (dbase)
select name from sys.databases
where name not in ('master','tempdb','model','msdb')

The problem is here when I try to test and see if I can switch to the first database in the table and perform a query:

select @dbase = dbase from #databases where x = 1
set @dbase = rtrim(@dbase)

use @dbase
go
select distinct customer_id from customers

Error Message -> Incorrect syntax near '@dbase'

Apparently assigning database names to variables is a bit more complicated than I suspected.

Can anyone provide a clue for how I can accomplish this task any other way or is there another way that I can assign the database name to a variable that could work???

[azimmer]

Hi.

I am having a problem trying to find the proper way to assign a system object (or rather in this case a system object name) to a variable to use in my T-SQL code.

I have to run a report across 20 or more databases and need to create a loop to run the same report execution on every database.

This is a snippet of the code I am trying to execute:
-- Create a temp table to store each database on the server in a table with a
-- corresponding incremental integer

create table #databases
(x int identity(1,1),dbase nvarchar(128))
go
insert into #databases (dbase)
select name from sys.databases
where name not in ('master','tempdb','model','msdb')

The problem is here when I try to test and see if I can switch to the first database in the table and perform a query:

select @dbase = dbase from #databases where x = 1
set @dbase = rtrim(@dbase)

use @dbase
go
select distinct customer_id from customers

Error Message -> Incorrect syntax near '@dbase'

Apparently assigning database names to variables is a bit more complicated than I suspected.

Can anyone provide a clue for how I can accomplish this task any other way or is there another way that I can assign the database name to a variable that could work???

Your problem is not with assigning the variable but with using it :). "USE" doesn't take variables as parameters. Try this:

declare @sqlstatement as varchar(255)
 
select @dbase = dbase from #databases where x = 1
set @dbase = rtrim(@dbase)
 
set @sqlstatement = 'USE ' + @dbase + ';'
set @sqlstatement = @sqlstatement + 'select distinct customer_id from customers;'
exec(@sqlstatement)
 
 

NB: Read EXEC's help carefully

[mivey4]

Your problem is not with assigning the variable but with using it :). "USE" doesn't take variables as parameters. Try this:

declare @sqlstatement as varchar(255)
 
select @dbase = dbase from #databases where x = 1
set @dbase = rtrim(@dbase)
 
set @sqlstatement = 'USE ' + @dbase + ';'
set @sqlstatement = @sqlstatement + 'select distinct customer_id from customers;'
exec(@sqlstatement)
 
 

NB: Read EXEC's help carefully

Thanks, I will. Your code worked great, the use of the required single quotes seem like it could get pretty messy very quick for more complex queries though. But if there is no other way; there's no other way.....