如何使用SQL存储过程创建数据库? [英] How to create a database using SQL stored procedure?

问题描述

如何使用SQL存储过程创建数据库?为什么我不能将数据库作为变量发送?

How to create a database using SQL stored procedure? Why can not I send the database as a variable?

从外部发送数据库名称是否有限制?如果有这样的限制，我该怎么做?

Is there a restriction on sending the database name from the outside? How can I do this if there is such a restriction?

create proc AddDatabase
    @Name varchar(100),
    @FileName varchar(Max),
    @Size int,
    @Maxsize int,
    @FileGrowth int,
    @logName varchar(100),
    @LogFileName varchar(Max),
    @LogSize int,
    @LogMaxsize int,
    @LogFileGrowth int)
as
begin
    Create database @Name 
    On Primary
        (NAME=@Name
         FileName=@FileName,
         Size=@size
         MaxSize=@Maxsize,
         FileGrowth=@FileGrowth
        )
   log on 
        (NAME=@logName,
         FILENAME=@LogFileName,
         SIZE=@LogSize,
         MAXSIZE=@LogMaxsize,
         FILEGROWTH=@LogFileGrowth
        )
end

推荐答案

其他都对，需要用到动态 SQL.这应该可以满足您的要求，但是请注意，我尚未对此进行测试.

The others are right, you need to use Dynamic SQL. This should get you what you're after, however, please note I have not tested this.

CREATE PROC AddDatabase @Name varchar(100),
                        @FileName varchar(MAX),
                        @Size int,
                        @MaxSize int,
                        @FileGrowth int,
                        @LogName varchar(100),
                        @LogFileName varchar(MAX),
                        @LogSize int,
                        @LogMaxsize int,
                        @LogFileGrowth int
AS
    DECLARE @SQL nvarchar(MAX), @Params nvarchar(MAX);
    SET @SQL = N'
    CREATE DATABASE ' + QUOTENAME(@Name) + N' ON PRIMARY
        (NAME = @dName,
         FILENAME = @dFileName,
         SIZE = @dSize,
         MAXSIZE = @dMaxSize,
         FILEGROWTH = @dFileGrowth)
    LOG ON (NAME = @dLogName,
            FILENAME = @dLogFileName,
            SIZE = @dLogSize,
            MAXSIZE = @dLogMaxsize,
            FILEGROWTH = @dLogFileGrowth);';
    SET @Params = N'@dName varchar(100), @dFileName varchar(MAX), @dSize int, @dMaxSize int, @dFileGrowth int, @dLogName varchar(100), @dLogFileName varchar(MAX), @dLogSize int, @dLogMaxsize int, @dLogFileGrowth int';
    EXEC sp_executesql @SQL, @Params, @dName = @Name, @dFileName = @FileName, @dSize = @Size, @dMaxSize = @MaxSize, @dFileGrowth = @FileGrowth, @dLogName = @LogName, @dLogFileName = @LogFileName, @dLogSize = @LogSize, @dLogMaxsize = @LogMaxsize, @dLogFileGrowth = @LogFileGrowth;
GO

这篇关于如何使用SQL存储过程创建数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！