SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: xujinli
-- Create date: 2011-12-7
-- Description: 创建数据库和表
-- =============================================
CREATE PROCEDURE P_Create
--@year 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]=@KuName ) --判断该数据是否存在
print '该数据库已经存在'
else
begin
create database @KuName --创建数据库名为:@KuName on primary
(name=@KuName,
filename='D:\Project\DataBase\'+@KuName+'.mdf',
size=20,
filegrowth=10%
), --创建日志,只有一个主数据文件@KuName.mdf
log on
(name=@KuName,
filename='D:\Project\DataBase\'+@KuName+'.ldf',
size=20,
filegrowth=10%
)
end END
GO
红色的部分说是有语法错误 why
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: xujinli
-- Create date: 2011-12-7
-- Description: 创建数据库和表
-- =============================================
CREATE PROCEDURE P_Create
--@year 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]=@KuName ) --判断该数据是否存在
print '该数据库已经存在'
else
begin
create database @KuName --创建数据库名为:@KuName on primary
(name=@KuName,
filename='D:\Project\DataBase\'+@KuName+'.mdf',
size=20,
filegrowth=10%
), --创建日志,只有一个主数据文件@KuName.mdf
log on
(name=@KuName,
filename='D:\Project\DataBase\'+@KuName+'.ldf',
size=20,
filegrowth=10%
)
end END
GO
红色的部分说是有语法错误 why
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: xujinli
-- Create date: 2011-12-7
-- Description: 创建数据库和表
-- =============================================
create procedure P_Create
--@year 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]=@KuName ) --判断该数据是否存在
print '该数据库已经存在'
else
begin
declare @sql varchar(5000)
select @sql='create database '+@KuName+' ON PRIMARY '
+'(name='''+@KuName+''',filename=''D:\Project\DataBase\'+@KuName+'.mdf'', '
+'size=20,filegrowth=10%) log on(name='''+@KuName+'_log'','
+'filename=''D:\Project\DataBase\'+@KuName+'.ldf'',size=20,filegrowth=10%)'
exec(@sql)
end END
go
create database abcd-- 删除测试库,正常.
drop database abcd-- 用变量做库名,出错.
declare @KuName nvarchar(50)
select @KuName='abcd'
create database @KuNameMsg 102, Level 15, State 1, Line 4
Incorrect syntax near '@KuName'.
-- 在存储过程中,建完数据库后执行.
exec('use '+@KuName+' create table [表名]([字段名] [数据类型]...)')
as
declare @year nvarchar(100)
declare @KuName nvarchar(200)
begin
begin transaction
select @year=cast(YEAR(getdate()) as nvarchar),
@KuName='XQ_'+Cast(YEAR(getdate()) as nvarchar(50))
if exists(select * from sys.databases where [name]=@KuName ) --判断该数据是否存在
print '该数据库已经存在'
else
begin
declare @sql varchar(5000) select @sql='create database '+@KuName+' ON PRIMARY '
+'(name='''+@KuName+''',filename=''D:\Project\DataBase\'+@KuName+'.mdf'', '
+'size=20,filegrowth=10%) log on(name='''+@KuName+'_log'','
+'filename=''D:\Project\DataBase\'+@KuName+'.ldf'',size=20,filegrowth=10%)'
exec(@sql)
declare @sqltable varchar(5000)
select @sqltable=' use '+@KuName+' go '
+' CREATE TABLE [dbo].[TB_RealData]('+
+' [Company_Code] [nchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,'
+' [Real_Data] [varchar](800) COLLATE Chinese_PRC_CI_AS NOT NULL,'
+' [Real_Date] [datetime] NULL,'
+' [Real_Type] [varchar](2) COLLATE Chinese_PRC_CI_AS NULL,'
+' [Real_Re] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL'
+' ) ON [PRIMARY]'
exec(@sqltable)
end
commit transaction /*提交事务*/
end
这个地方有错误 ?