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.

10 Years
Discussion Span
Last Post by hollystyles

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

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.