Attentions About Creating Database through Store Procedure

来源：互联网发布：阿里云改系统编辑：程序博客网时间：2024/04/27 18:14

When create a database, the following items must be pay more attention.

Firstly, you'd better specifies SQL-92 compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values.
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

Secondly, you must write the parameters of this procedure correctly.

    Create PROCEDURE [dbo].[CreateExpmpleDatabase] (
                                          @DatabaseName NVARCHAR(255), -->database name you want to create
                                          @DataLocation NVARCHAR(1024),    -->data file location
                                          @LogLocation NVARCHAR(1024),      -->log file location
                                          @DataSize NVARCHAR(50),                -->the data size of the database
                                          @DataGrowth NVARCHAR(50) )        -->the database growth style
    AS

Then you'd better check whether the database already exists or not.

   IF EXISTS (SELECT name FROM   master.dbo.sysdatabases   WHERE name = @DatabaseName)
   BEGIN
   RAISERROR('The database you want to create has already exists.', 16,1)
   RETURN 0
   End

Secondly, you should convert the database name you passed in to a valid Microsoft SQL Server 2005 delimited identifier by using the quotename(). This identifier is a unicode string.

   declare @tempDBName NVARCHAR(500)
   set       @tempDBName = quotename(@DatabaseName)

Thirdly, prepare the creating command string.

   DECLARE   @createString    nvarchar(4000)
   SET                @createString    = 'CREATE DATABASE ' +   @tempDBName
                                                            ' ON (NAME = ' + dbo.quotestring(@DatabaseName) +
      ', FILENAME = ' + dbo.quotestring(@Datalocation) +
      ', SIZE = '       + @DataSize +
                                                           ', FILEGROWTH = ' + @DataGrowth + ')'
         ' LOG ON (NAME = ' + dbo.quotestring(@DatabaseName + '_log') +
                                                           ', FILENAME = ' + dbo.quotestring(@Loglocation) + ')'

Then get the database name by using DB_NAME() .

    declare @testDatabaseName nvarchar(600)
    set         @testDatabaseName = (SELECT DB_NAME() )

Then get the collation of the database by using CAST(DATABASEPROPERTYEX(@testDatabaseName , N'Collation') and execute the command.

   DECLARE @collateString nvarchar(1024)
   SET @collateString = (SELECT CAST(DATABASEPROPERTYEX(@testDatabaseName ,   N'Collation')                                        AS    CHAR(50)))
   SET @createString = @createString + ' COLLATE ' + @collateString

    execute(@createString)

If the execution is failed, an error will be raised.

   IF @@ERROR != 0
   BEGIN
   RAISERROR('Database creation failed', 16,1)
   RETURN 0
   End

The last step is to set the database options. Such AUTO_CLOSE OFF, AUTO_SHRINK OFF

   execute( 'ALTER DATABASE ' + @escapedDBName + ' SET AUTO_CLOSE OFF')
    execute( 'ALTER DATABASE ' + @escapedDBName + ' SET AUTO_SHRINK OFF')
    execute( 'ALTER DATABASE ' + @escapedDBName + ' SET ANSI_NULLS OFF')
    execute( 'ALTER DATABASE ' + @escapedDBName + ' SET ANSI_WARNINGS OFF')
   execute( 'ALTER DATABASE ' + @escapedDBName + ' SET ANSI_NULL_DEFAULT OFF')
    execute( 'ALTER DATABASE ' + @escapedDBName + ' SET AUTO_CREATE_STATISTICS ON')
   execute( 'ALTER DATABASE ' + @escapedDBName + ' SET AUTO_UPDATE_STATISTICS ON')
   execute( 'ALTER DATABASE ' + @escapedDBName + ' SET CONCAT_NULL_YIELDS_NULL OFF')
    execute( 'ALTER DATABASE ' + @escapedDBName + ' SET MULTI_USER')
    execute( 'ALTER DATABASE ' + @escapedDBName + ' SET QUOTED_IDENTIFIER OFF')
   execute( 'ALTER DATABASE ' + @escapedDBName + ' SET READ_WRITE')
   execute( 'ALTER DATABASE ' + @escapedDBName + ' SET RECURSIVE_TRIGGERS OFF')
    execute( 'ALTER DATABASE ' + @escapedDBName + ' SET RECOVERY SIMPLE')

   RETURN 1

Above all, when we create a database by store procedure, the steps narrated above should be pay more attention on.