Monday, January 8, 2018

SQL SERVER - How execute script block code from script

/* -- script for DB creation */

declare @script_create nvarchar( max );

/* MSSQL 2014 */
if @iProductVersion = 12 
  begin
    set @script_create =
      'create database {DATABASE_NAME} on primary 
       ( name = ''{DATABASE_NAME}'', 
         filename = ''{FILE}'', 
         size = 5120KB, 
         maxsize = UNLIMITED, 
         filegrowth = 1024KB 
       )
       LOG ON 
       ( name = ''{DATABASE_NAME_LOG}'', 
         filename = ''{FILE_LOG}'', 
         size = 1024KB, 
         maxsize = 2048GB, 
         filegrowth = 10%
       ) 
       collate Czech_CI_AS
      '
    ;
  end
    else
  begin
    set @script_create =
      'create database {DATABASE_NAME} on primary 
       ( name = ''{DATABASE_NAME}'', 
         filename = ''{FILE}'', 
         size = 3072KB, 
         maxsize = UNLIMITED, 
         filegrowth = 1024KB 
       )
       LOG ON 
       ( name = ''{DATABASE_NAME_LOG}'', 
         filename = ''{FILE_LOG}'', 
         size = 1024KB, 
         maxsize = 2048GB, 
         filegrowth = 10%
       ) 
       collate Czech_CI_AS
      '
    ;
  end;

set @script_create = REPLACE( @script_create, '{DATABASE_NAME}', @database_name );
set @script_create = REPLACE( @script_create, '{DATABASE_NAME_LOG}', @database_name_log );
set @script_create = REPLACE( @script_create, '{FILE}', @file );
set @script_create = REPLACE( @script_create, '{FILE_LOG}', @file_log );

/* - call execute() */

execute( @script_create );

No comments:

Post a Comment