>I am writing a script that creates my database, stored procedures, tables, user data types etc. The script is over 800 lines and all works perfectly if I hard code the database name on the USE line. Here's the start of it:
>
>-- Create the upower database
>declare @dbname varchar(30)
>declare @sql varchar(200)
>
>set @dbname = 'utestbase'
>set @sql = 'create database ' + @dbname
>
>exec (@sql)
>go
>
>use utestbase
>go
>
>Now, obviously I want to pass in the "utestbase" value or any other value when I run the script. I just can't figure out how to execute the USE command with a variable, i.e. use @dbname.
>
>I've seen posts on other websites that describe using dynamic sql, as I have done for the create database function. However, it appears that in order to have the USE command in dynamic SQL you have to include the entire script in the batch. I can't do that because, when I try to construct the statement with concatenation, apostrophes just won't work. As you can imagine I have apostrophes all through my code.
>
>How can I set my dynamically named database as the active database before executing my 800 lines of code to create tables and stored procedures, etc?
>
>Thank you.

Elgin,

That's one of the common questions on MSDN T-SQL forum and here is a trick I learned from Erland Sommarskog
http://www.sommarskog.se/dynamic_sql.html

Relevant MSDN thread
------------------
I quote from his answer:

Rather than interleaving the database name in the query string, you can run with a fixed query string. Then you can do:

DECLARE @sp_executesql nvarchar(140)
SELECT @sp_executesql = @yourdb + '..sp_executesql'
EXEC @sp_executesql = @sql, @params, @vartoreturn OUTPUT

That is, if you say:

EXEC thatdb..sp_executesql
The dynamic SQL Statement is executed in thatdb; the same applies to all system procedures.

And when you say "EXEC @somesp", you run the procedure named by the variable @somesp.