Creating Schema | Giving Error | Dynamic Query

• calltoaneesh

  Old Hand

  Points: 319

  More actions

  July 31, 2008 at 12:51 am

  #194986

  Hi,

  I am using the following script for creating a schema

  set @Query='

  USE ['+@DatabaseName+']

  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'''+@SchemaName+''')

  CREATE SCHEMA ['+@SchemaName+'] AUTHORIZATION ['+@UserName+']

  '

  exec (@Query)

  While executing its giving error "Incorrect syntax near the keyword 'SCHEMA". ..Please help me out to resolve this...

  Thanks,

  Aneesh

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  July 31, 2008 at 3:19 am

  #851100

  You've already posted this same question in another thread, please don't cross post!

  http://www.sqlservercentral.com/Forums/Topic534176-359-1.aspx

  You need to check for the existence of the schema, and then execute the create statement dynamically...

  declare @query varchar(255)

  declare @schemaname varchar(255)

  set @schemaname = 'FRED'

  declare @username varchar(255)

  set @username = 'FRED'

  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @SchemaName)

  BEGIN

  set @Query='CREATE SCHEMA ['+@SchemaName+'] AUTHORIZATION ['+@UserName+']'

  exec (@Query)

  END