在存储过程中创建数据库及表
数据库的名称通过参数传递
create PROCEDURE SP_Build_ImageDB(
@year varchar(4)
)

BEGIN TRANSACTION
exec( 'create database UCMLSample '+@year)

exec( 'use UCMLSample '+@year+ '
CREATE TABLE Sample_Image (
Sample_ImageOID uniqueidentifier NOT NULL ,
ImageName varchar (100) COLLATE Chinese_PRC_CI_AS NULL ,
ImageData image NULL ,
ImageInfo varchar (100) COLLATE Chinese_PRC_CI_AS NULL ,
Sample_Samples_FK uniqueidentifier NULL ,
PRIMARY KEY CLUSTERED
(
Sample_ImageOID
) ON PRIMARY
) ON PRIMARY TEXTIMAGE_ON PRIMARY
')
exec( 'use UCMLSample '+@year+ '
insert into Sample_Image .......
')

if @@ERROR <> 0
begin
select @ErrNum = @@ERROR, @ErrMsg = '生成失败！ '
ROLLBACK TRANSACTION
select @ErrNum 'ErrNum ', @ErrMsg 'ErrMsg '
RETURN @@ERROR
end

COMMIT TRANSACTION

编译虽然通过了,但执行时会报错,请教各位有没有其他的好方法?

[解决办法]
create PROCEDURE SP_Build_ImageDB(
@year varchar(4)
)

BEGIN TRANSACTION
exec( 'use master go create database UCMLSample '+@year)

exec( 'go use UCMLSample '+@year+ '
go CREATE TABLE Sample_Image (
Sample_ImageOID uniqueidentifier NOT NULL ,
ImageName varchar (100) COLLATE Chinese_PRC_CI_AS NULL ,
ImageData image NULL ,
ImageInfo varchar (100) COLLATE Chinese_PRC_CI_AS NULL ,
Sample_Samples_FK uniqueidentifier NULL ,
PRIMARY KEY CLUSTERED
(
Sample_ImageOID
) ON PRIMARY
) ON PRIMARY TEXTIMAGE_ON PRIMARY
go ')
exec( 'use UCMLSample '+@year+ '
insert into Sample_Image .......
')

if @@ERROR <> 0
begin
select @ErrNum = @@ERROR, @ErrMsg = '生成失败！ '
ROLLBACK TRANSACTION
select @ErrNum 'ErrNum ', @ErrMsg 'ErrMsg '

RETURN @@ERROR
end

COMMIT TRANSACTION

[解决办法]
--try

create PROCEDURE SP_Build_ImageDB(
@year varchar(4)
)
as
declare @ErrNum int , @ErrMsg int
exec( 'create database UCMLSample '+@year)
exec( 'use UCMLSample '+@year+ '
CREATE TABLE Sample_Image (
Sample_ImageOID uniqueidentifier NOT NULL ,
ImageName varchar (100) COLLATE Chinese_PRC_CI_AS NULL ,
ImageData image NULL ,
ImageInfo varchar (100) COLLATE Chinese_PRC_CI_AS NULL ,
Sample_Samples_FK uniqueidentifier NULL ,
PRIMARY KEY CLUSTERED
(
Sample_ImageOID
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
')

exec( 'insert into Sample_Image ....... ')
go