SQL存储过程的创建和使用

存储过程创建

create procedure 存储过程名
as
sql语句

 

go
create procedure bookbooks(@bno int, @sno varchar(12))
as
begin transaction
declare @state int
select @state = Bstate from book where Bno = @bno
if @state in (1 ,3)
	begin
		rollback
	end
else if @state = 0
	begin
		update book set Bstate = 1 where Bno = @bno
		insert into Bbook 
		(Bdate, Bno, Sno)
		values
		(GETDATE(), @bno, @sno)
	end
else if @state = 2
	begin
		update book set Bstate = 3 where Bno = @bno
		insert into Bbook 
		(Bdate, Bno, Sno)
		values
		(GETDATE(), @bno, @sno)
	end
commit
go

go
create procedure renwebook(@bno int, @sno varchar(12), @aid varchar(12))
as
begin transaction
	declare @state int
	select @state = bstate from book where Bno = @bno
	if(@state in (0, 1, 3))
		begin
			rollback
		end
	else if(@state = 2)
		begin
			insert into Rbook
			(Aid, Bdate, Bno, Sno)
			values
			(@aid, GETDATE(), @bno, @sno)
			update book set Bstate = 3 where Bno = @bno
		end
commit
go

存储过程使用

exec dbo.bookbooks 参数1 = 值1, 参数2 = 值2, ...