数据库的名称通过参数传递
create PROCEDURE SP_Build_ImageDB(
@year varchar(4)
)
BEGIN TRANSACTION
exec( 'create database UCMLSample '[email protected])
exec( 'use UCMLSample '[email protected]+ '
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 '[email protected]+ '
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 '[email protected])
exec( 'go use UCMLSample '[email protected]+ '
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 '[email protected]+ '
insert into Sample_Image .......
')
if @@ERROR <> 0
begin
select @ErrNum = @@ERROR, @ErrMsg = '生成失败！ '
ROLLBACK TRANSACTION
select @ErrNum 'ErrNum ', @ErrMsg 'ErrMsg '