cgodoy


I need use a variable in the next situation:

select * from @databasename..tablename

The database name is in a .ini file.

Have any option to implement this situation

Thanks in advance

Chama




Re: if posible use a database name in a variable?

SNMSDN


You'd have to use dynamic SQL to do that. Something like this

declare @sql nvarchar(4000), @MyDatabase nvarchar(100)
set @MyDatabase = N'pubs'
set @sql = N'SELECT * FROM ' + @MyDatabase + '.dbo.authors'
exec sp_executesql @sql

Are you going to be sending the SQL statements from a client If so, it would be much better to set the database in the connection string and then use regular SQL statements than using dynamic SQL everywhere.






Re: if posible use a database name in a variable?

cgodoy

I change my SP in two SP, one for each database.

The sql dynamic alternative is not attractive for my.

Thanks por your help.






Re: if posible use a database name in a variable?

pons68

You can call a SP from your SP to set the database:

CREATE PROCEDURE usp_SetDB

@DBID INT

AS

IF @DBID = 1

USE <DB1>

IF @DBID = 2

USE <DB2>

Then remove the database reference from your SPs. That should work.