CODEWITHSUNDEEP
sqlsql-server
pufos
pufos

Reputation: 2930

sql create database from variable

I have @db_out = 'aux.dbo.some_table_name' , and I don't know how to drop , create based on that variable like : 

IF OBJECT_ID(@db_out) IS NOT NULL DROP TABLE "@db_out" - not working
CREATE TABLE "@db_out"  .... etc

it creates master.dbo.@dbo_out

How can I use that variable to create that table or verify it and drop it ?

Upvotes: 1

Views: 226

Answers (2)

MatBailie
MatBailie

Reputation: 86706

You need to write dynamic SQL.

SET @sql = 'IF OBJECT_ID(@db_out) IS NOT NULL DROP TABLE ' + @db_out + '; '
SET @sql = @sql + 'CREATE TABLE ' + @db_out + ' (...)'
EXEC(@sql)

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

You will have to build the statement in varchar variable and execute it:

declare @strSql as varchar(max)

IF OBJECT_ID(@db_out) IS NOT NULL
BEGIN
   EXEC ('DROP TABLE [' + @db_out + ']')
END
set @strSql = 'CREATE TABLE [' + @db_out + '] (' -- Etc
EXEC (@strSql)

Upvotes: 3

Related Questions