Member Avatar for clonyz

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.

  • 2 Contributors
  • 1 Reply
  • 74 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by hollystyles
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.