Defining Database with Local Variable

[gammatron]

I am trying to dynamically set the Database Name using a local variable...

I want to give access to a user to set the database name (New DBName Each Month) but not have access to the Script I'm writing... so I'm thinking the following

Declare @DBName nvarchar (255)
USE DBNameDatabase
Select @DBName = DBName From DBNameTable
Use @DBName

--Rest of script using @DBName as Database


Obviously doesn't work... any help would be appreciated

Thx

[actd]

Anyone out there have any ideas on this - I'd like to do exactly the same thing :-)

[iburyak]

Where is your create database statement?

USE master

CREATE DATABASE @DBName

[actd]

Ah, a misunderstandin g - it's the USE statement I want to be dynamic, and

USE @DBName just doesn't seem to work.

Any ideas would be greatly appreciated.

Where is your create database statement?

USE master

CREATE DATABASE @DBName

[almaz]

I am trying to dynamically set the Database Name using a local variable...

Unfortunately you cannot dynamically change the database. All you can do is to prepare dynamic SQL and execute it:

declare @a nvarchar(4000)
set @a = 'master'
exec ('use ' + @a + ' select * from sysobjects')

[actd]

That might do the trick - simple when you know how :-). I'll give it a go when I get a chance. Many thanks for this solution (assuming it does work for me).

Unfortunately you cannot dynamically change the database. All you can do is to prepare dynamic SQL and execute it:

declare @a nvarchar(4000)
set @a = 'master'
exec ('use ' + @a + ' select * from sysobjects')

[gkka321]

That might do the trick - simple when you know how :-). I'll give it a go when I get a chance. Many thanks for this solution (assuming it does work for me).

yes definitely it works