Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Angry Unanswered: how do you create a database within a stored procedure

    I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'

    select @iStatus = 0
    select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)


    create database @dbname
    on
    ( name = @dbname + 'data',
    filename = 'F:\MSSQL\Data\' + @dbname + 'data.mdf',
    size = 10mb,
    filegrowth = 10% )
    log on
    ( name = @dbname + 'log',
    filename = 'F:\MSSQL\Data\' + @dbname + 'log.ldf',
    size = 5mb,
    filegrowth = 10% )

    select @iStatus = @@error
    if @iStatus = 0
    print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
    else
    print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)

    end

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that you want:
    PHP Code:
    select @iStatus 0
    select 
    @dbname 'offline' '_' convert(char(6),getdate(),112)

    EXECUTE ('create database @dbname
    on
    ( name = ' 
    + @dbname ' + ''data'',
    filename = ''F:\MSSQL\Data\' + @dbname + '
    data.mdf'',
    size 10mb,
    filegrowth 10% )
    log on
    name ''' + @dbname + 'log'',
    filename ''F:\MSSQL\Data\' + @dbname + 'log.ldf'',
    size 5mb,
    filegrowth 10% )')

    select @iStatus = @@error
    if @iStatus = 0
    print '
    Part 1Database offline_' + @dbname + 'has been created successfully'
    else
    print '
    Part 1Database Offline_' + @dbname + 'failed to createstatus ' + convert(varchar(10), @iStatus)

    end 
    You'll need to check this, since I'm composing "on the fly" and have no way to double check the syntax changes that I made.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    5

    Re: how do you create a database within a stored procedure

    thanks... I'll have a go


    Originally posted by bunmiA
    I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'

    select @iStatus = 0
    select @dbname = 'offline' + '_' + convert(char(6),getdate(),112)


    create database @dbname
    on
    ( name = @dbname + 'data',
    filename = 'F:\MSSQL\Data\' + @dbname + 'data.mdf',
    size = 10mb,
    filegrowth = 10% )
    log on
    ( name = @dbname + 'log',
    filename = 'F:\MSSQL\Data\' + @dbname + 'log.ldf',
    size = 5mb,
    filegrowth = 10% )

    select @iStatus = @@error
    if @iStatus = 0
    print 'Part 1: Database offline_' + @dbname + 'has been created successfully'
    else
    print 'Part 1: Database Offline_' + @dbname + 'failed to create, status ' + convert(varchar(10), @iStatus)

    end

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Whew! Let's try that again now that I can see the whole thing! I missed a few important points.
    PHP Code:
    select @iStatus 0
    select 
    @dbname 'offline' '_' convert(char(6),getdate(),112)

    EXECUTE ('create database ' + @dbname ' on
    ( name = ''' 
    + @dbname 'data'',
    filename = ''F:\MSSQL\Data\' + @dbname + '
    data.mdf'',
    size 10mb,
    filegrowth 10% )
    log on
    name ''' + @dbname + 'log'',
    filename ''F:\MSSQL\Data\' + @dbname + 'log.ldf'',
    size 5mb,
    filegrowth 10% )')

    select @iStatus = @@error
    if @iStatus = 0
    print '
    Part 1Database offline_' + @dbname + ' has been created successfully'
    else
    print '
    Part 1Database Offline_' + @dbname
    +  ' 
    failed to createstatus ' + convert(varchar(10), @iStatus)

    end 
    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •