输入的参数涉及到三个表分别是:
Items(Name,ImageFileSpec,Description,UnitPrice),
Books(PublisherId,Author,ISBN,Subject),
ItemCategory(CategoryId),其中ItemCategory中还包括ItemId这一字段,最后把@ItemId作为输出参数。
/**********新增图书**************/0:注册成功,1:注册失败
Create procedure ADDBook
(
@Name nvarchar(255),
@ImageFileSpec nvarchar(255),
@Description nvarchar(2000),
@UnitPrice money,
@PublisherId int(10),
@Author nvarchar(40),
@ISBN nchar(13),
@Subject nvarchar(255),
@CategoryId int(10)
)
as
if exists(select Name,ImageFileSpec,Description,UnitPrice from Items where
Name=@Name,ImageFileSpec=@ImageFileSpec,Description=@Description,UnitPrice=@UnitPrice)
insert into Items(Name,ImageFileSpec,Description,UnitPrice)
values(@Name,@ImageFileSpec,@Description,@UnitPrice)
if @@error=0
return 1
else
return 0
if exists(select PublisherId,Author,ISBN,Subject from Books where PublisherId=@PublisherId,Author=@Author,ISBN=@ISBN,Subject=@Subject)
insert into Books(PublisherId,Author,ISBN,Subject)
values(@PublisherId,@Author,@ISBN,@Subject)
if @@error=0
return 1
else
return 0
if exists(select CategoryId from ItemCategory where CategoryId=@CategoryId)
insert into ItemCategory(CategoryId) values(@CategoryId)
if @@error=0
return 1
else
return @ItemId
go
Items(Name,ImageFileSpec,Description,UnitPrice),
Books(PublisherId,Author,ISBN,Subject),
ItemCategory(CategoryId),其中ItemCategory中还包括ItemId这一字段,最后把@ItemId作为输出参数。
/**********新增图书**************/0:注册成功,1:注册失败
Create procedure ADDBook
(
@Name nvarchar(255),
@ImageFileSpec nvarchar(255),
@Description nvarchar(2000),
@UnitPrice money,
@PublisherId int(10),
@Author nvarchar(40),
@ISBN nchar(13),
@Subject nvarchar(255),
@CategoryId int(10)
)
as
if exists(select Name,ImageFileSpec,Description,UnitPrice from Items where
Name=@Name,ImageFileSpec=@ImageFileSpec,Description=@Description,UnitPrice=@UnitPrice)
insert into Items(Name,ImageFileSpec,Description,UnitPrice)
values(@Name,@ImageFileSpec,@Description,@UnitPrice)
if @@error=0
return 1
else
return 0
if exists(select PublisherId,Author,ISBN,Subject from Books where PublisherId=@PublisherId,Author=@Author,ISBN=@ISBN,Subject=@Subject)
insert into Books(PublisherId,Author,ISBN,Subject)
values(@PublisherId,@Author,@ISBN,@Subject)
if @@error=0
return 1
else
return 0
if exists(select CategoryId from ItemCategory where CategoryId=@CategoryId)
insert into ItemCategory(CategoryId) values(@CategoryId)
if @@error=0
return 1
else
return @ItemId
go
Create procedure ADDBook
(
@Name nvarchar(255),
@ImageFileSpec nvarchar(255),
@Description nvarchar(2000),
@UnitPrice money,
@PublisherId int(10),
@Author nvarchar(40),
@ISBN nchar(13),
@Subject nvarchar(255),
@CategoryId int(10)
)
as
if not exists(select Name,ImageFileSpec,Description,UnitPrice from Items where
Name=@Name,ImageFileSpec=@ImageFileSpec,Description=@Description,UnitPrice=@UnitPrice)
insert into Items(Name,ImageFileSpec,Description,UnitPrice)
values(@Name,@ImageFileSpec,@Description,@UnitPrice)
if @@error=0
return -1 if not exists(select PublisherId,Author,ISBN,Subject from Books where
PublisherId=@PublisherId,Author=@Author,ISBN=@ISBN,Subject=@Subject)
insert into Books(PublisherId,Author,ISBN,Subject)
values(@PublisherId,@Author,@ISBN,@Subject)
if @@error=0
return -1
if not exists(select CategoryId from ItemCategory where CategoryId=@CategoryId)
insert into ItemCategory(CategoryId) values(@CategoryId)
if @@error=0
return -1
return @@identity
go
表ItemCategory的两个字段都不是主键。
服务器: 消息 170,级别 15,状态 1,过程 ADDBook,行 15
第 15 行: ',' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,过程 ADDBook,行 23
第 23 行: ',' 附近有语法错误。
也就是
where Name=@Name,ImageFileSpec=@ImageFileSpec,Description=@Description,UnitPrice=@UnitPrice
和
where PublisherId=@PublisherId,Author=@Author,ISBN=@ISBN,Subject=@Subject
这两行有错
alter procedure ADDBook
(
@Name nvarchar(255),
@ImageFileSpec nvarchar(255),
@Description nvarchar(2000),
@UnitPrice money,
@PublisherId int,
@Author nvarchar(40),
@ISBN nchar(13),
@Subject nvarchar(255),
@CategoryId int
)
as
declare @itemid int
BEGIN TRANSACTION insert into Items(Name,ImageFileSpec,Description,UnitPrice) values(@Name,@ImageFileSpec,@Description,@UnitPrice) insert into Books(PublisherId,Author,ISBN,Subject) values(@PublisherId ,@Author,@ISBN,@Subject) insert into ItemCategory(CategoryId) values(@CategoryId) select @itemid = max(itemid) from ItemCategoryIF @@error<>0
ROLLBACK TRANSACTION
begin
COMMIT TRANSACTION
return @itemid
end