使用存储过程操作数据表

go use db_Test;if exists (select * from sysobjects where name='book')drop table book;create table book(bookId int primary key,bookName char(20),bookPrice float,publishTime date);--创建存储过程,向book表中插入10条数据goif exists (select * from sysobjects where name='addBook')drop procedure addBook;gocreate procedure addBook asbegindeclare @i int;set @i=1;while(@i<10)begin--cast 将一个表达式转化成另外一个类型insert into book select @i,'book '+cast(@i as CHAR(1)),(10+@i)/10.0,GETDATE();set @i=@i+1;endendgo--执行存储过程exec addBook;--创建带参数的存储过程：@id表示所借书ID @money表示钱，result 表示借书结果if exists(select * from sysobjects where name='borrowBook')drop procedure borrowBook;gocreate procedure borrowBook @id int,@money float,@result bit output asbegindeclare @bookPrice float;--获取所借书本的价格select @bookPrice=bookPrice from book where bookId=@id;if(@money>=@bookPrice)beginset @result=1;print '可以借书';endelsebeginset @result=0;print '不能借书';endend go--执行存储过程declare @result bit;--使用output 返回参数exec borrowBook 1,1.3,@result output;select case when @result=0 then '不能借书'when @result=1 then '可以借书' end as '能否借书';--修改存储过程：将borrowBook的返回值的类型改为chargoalter procedure borrowBook @id int,@money float,@result char(20) output asbegindeclare @bookPrice float;--获取所借书本的价格select @bookPrice=bookPrice from book where bookId=@id;if(@money>=@bookPrice)beginset @result='可以借书';endelsebeginset @result='不能借书';endend godeclare @re char(20);exec borrowBook 1,1.2,@re output;select @re as '能否借书';