Help: Make Database Name as Paramter Value

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 426,478 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,180 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 1499 | Replies: 1

•

Join Date: Aug 2007

Posts: 1

Reputation: clonyz is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

clonyz

Offline

Newbie Poster

Help: Make Database Name as Paramter Value

Aug 15th, 2007

dear to all,

i want to make my database name as a dynamic one which i'll make it as as a parameter value in a stored procedure.

pls help me of what the codes should be. any hint or something.

i've tried:

declare @data varchar(10)
set @data = dbName
USE @data

this is not working. error shows. pls help.

•

Join Date: Feb 2005

Location: Braintree, UK

Posts: 1,165

Reputation: hollystyles will become famous soon enough

Rep Power: 7

Solved Threads: 59

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Help: Make Database Name as Paramter Value

Aug 15th, 2007

You have to concatonate a TSQL string first and then execute it dynamically.

Help with Code Tags

(Toggle Plain Text)

declare @tsql nvarchar(4000)
declare @dbName varchar(32)

set @dbName = 'dbName'

set @tsql = 'use ' + @dbName + '; '
set @tsql = @tsql + 'select * from table'

exec sp_executesql @tsql

When using the USE keyword to change database context it is only valid until sp_executesql has completed after which the context is returned to the database in which you are running this TSQL. That is why I include an example select statement, it has to be done all in one string to work. The TSQL string HAS to be an nvarchar datatype sp_executesql requires that. Read more details in books online that comes with Sql Server.

==========================================
Yadda yadda yadda...
Web junky, fevered monkey