SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: xujinli
-- Create date: 2011-12-7
-- Description: 创建数据库和表
-- =============================================
CREATE PROCEDURE P_Create
[email protected] nvarchar(100)//这样的写法是传进来的参数
AS
declare @year nvarchar(100),
@KuName nvarchar(200)
BEGIN
select @year=YEAR(getdate()),
@KuName='XQ_'+Cast(YEAR(getdate()) as nvarchar(50))
if exists(select * from sys.databases where [name][email protected] ) --判断该数据是否存在
print '该数据库已经存在'
else
begin
create database @KuName --创建数据库名为:@KuName on primary
([email protected],
filename='D:\Project\DataBase\[email protected]+'.mdf',
size=20,
filegrowth=10%
), --创建日志,[email protected]
log on
([email protected],
filename='D:\Project\DataBase\[email protected]+'.ldf',
size=20,
filegrowth=10%
)
end
END
GO
红色的部分说是有语法错误 why
------解决方案--------------------
create database 后面要加实际的名称,不能用变量.
------解决方案--------------------
用变量创建数据库？
------解决方案--------------------
这个得拼SQL语句吧。
------解决方案--------------------
用要动态SQL,
- SQL code
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: xujinli-- Create date: 2011-12-7-- Description: 创建数据库和表-- =============================================create procedure P_Create[email protected] nvarchar(100)//这样的写法是传进来的参数AS declare @year nvarchar(100), @KuName nvarchar(200)BEGIN select @year=cast(YEAR(getdate()) as nvarchar), @KuName='XQ_'+Cast(YEAR(getdate()) as nvarchar(50)) if exists(select * from sys.databases where [name][email protected] ) --判断该数据是否存在 print '该数据库已经存在'elsebegin declare @sql varchar(5000) select @sql='create database [email protected]+' ON PRIMARY ' +'([email protected]+''',filename=''D:\Project\DataBase\[email protected]+'.mdf'', ' +'size=20,filegrowth=10%) log on([email protected]+'_log'',' +'filename=''D:\Project\DataBase\[email protected]+'.ldf'',size=20,filegrowth=10%)' exec(@sql) end ENDgo
------解决方案--------------------
不行,一定要用动态SQL,举个最简单的例子,
- SQL code
-- 建测试库,正常.create database abcd-- 删除测试库,正常.drop database abcd-- 用变量做库名,出错.declare @KuName nvarchar(50)select @KuName='abcd'create database @KuNameMsg 102, Level 15, State 1, Line 4Incorrect syntax near [email protected]'.
------解决方案--------------------
要用动态语句创建库表
------解决方案--------------------
- SQL code
-- 在存储过程中,建完数据库后执行.exec('use [email protected]+' create table [表名]([字段名] [数据类型]...)')
------解决方案--------------------
不需要"go", 删除即可.
请看10楼代码.