这个是我的存储过程代码
CREATE PROCEDURE createDB
-- Add the parameters for the stored procedure here
@DBname varchar(20) = 'dqleakdb_test'
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- 如果已经存在需要创建的数据库,则先删除了,然后再重新创建
IF exists (SELECT * FROM sys.databases WHERE name = @DBname)
Exec ( 'drop database' + @DBname )
DECLARE @s varchar(2000)
set @s = '
CREATE DATABASE ' + @DBname +'
ON
(
NAME = ' + @DBname + ',
FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' + @DBname + '.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5
)
LOG ON
(
NAME = ' + @DBname + ',
FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' + @DBname + 'Log.mdf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
'
EXEC ( @s )
END
GO
这个是执行代码
EXEC createDB 'abcsd'
但是却出现了
消息 1828,级别 16,状态 5,第 2 行
逻辑文件名 "abcsd" 已被使用。请选择另一个名称。而且 EXEC createDB 运行后
出现
消息 1828,级别 16,状态 5,第 2 行
逻辑文件名 "dqleakdb_test" 已被使用。请选择另一个名称。请问该怎么解决啊?
谢谢了啊
CREATE PROCEDURE createDB
-- Add the parameters for the stored procedure here
@DBname varchar(20) = 'dqleakdb_test'
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- 如果已经存在需要创建的数据库,则先删除了,然后再重新创建
IF exists (SELECT * FROM sys.databases WHERE name = @DBname)
Exec ( 'drop database' + @DBname )
DECLARE @s varchar(2000)
set @s = '
CREATE DATABASE ' + @DBname +'
ON
(
NAME = ' + @DBname + ',
FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' + @DBname + '.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5
)
LOG ON
(
NAME = ' + @DBname + ',
FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\' + @DBname + 'Log.mdf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)
'
EXEC ( @s )
END
GO
这个是执行代码
EXEC createDB 'abcsd'
但是却出现了
消息 1828,级别 16,状态 5,第 2 行
逻辑文件名 "abcsd" 已被使用。请选择另一个名称。而且 EXEC createDB 运行后
出现
消息 1828,级别 16,状态 5,第 2 行
逻辑文件名 "dqleakdb_test" 已被使用。请选择另一个名称。请问该怎么解决啊?
谢谢了啊
将第二个改为NAME = ' + @DBname + '_Log,另外 Exec ( 'drop database' + @DBname ) 要有空格
应该为: Exec ( 'drop database ' + @DBname )
http://www.cnblogs.com/wangfang224120999/archive/2009/02/25/1397606.html
将第一个改为NAME = ' + @DBname + '_Data,
将第二个改为NAME = ' + @DBname + '_Log,
这里可以随便自己命名,且名称唯一即可。
Exec ( 'drop database' + @DBname ) ---修改前,如果传入的@DBname是sql里面的关键字会报错
Exec ( 'drop database [' + @DBname+ ']' )---修改后,添加中括号