存储过程 use EBuy go --常用的系统存储过程 sp_addmessage --将新的用户定义错误消息存储在SQL Server数据库实例中 sp_helptext --显示用户定义的规则、默认值、存储过程、函数、视图等对象的实际文本 sp_xml_preparedocument --读取作为输入提供的XML文本，然后使用MSXML分析器对其进行分析，并提供分析后的文档供使用 sp_helpdb 'EBuy' --查看指定数据库的信息 sp_databases --列出服务器上的所有数据库 sp_server_info --列出服务器信息，如字符集、版本和排列顺序等 sp_stored_procedures --列出当前环境中的所有存储过程 sp_configure --修改SQL Server全局配置选项 sp_adduser --向当前数据库中添加新的用户 sp_addrole --在当前数据库中创建新的数据库角色 --用户自定义存储过程 create table users( id int primary key, name varchar(20) not null, pwd varchar(20) not null ) insert into users values(1,'zhangsan','zhang01') go create procedure addUser --创建存储过程(procedure可以简写为proc) @id int, @name varchar(20), @pwd varchar(20) as begin insert into users(id,name,pwd) values(@id,@name,@pwd) end exec addUser 2,'lisi','lisi01' --执行存储过程(实参传递的顺序和所对应的形参顺序必须一致) exec addUser @name='wangwu',@pwd='wang01',@id=3 --执行存储过程，这种方式顺序可以不一致 declare @v_id int,@v_name varchar(20),@v_pwd varchar(20) set @v_id=5 set @v_name='zhaoliu2' set @v_pwd='zhao022' exec addUser @v_id,@v_name,@v_pwd --执行存储过程，调用存储过程并传递变量参数(顺序必须与定义变量的顺序一致) select * from users
２ --制定默认值 use Study if exists(select * from sys.all_objects where name='increaseCredit' and type='P') drop procedure increaseCredit go create proc increaseCredit @stuName char(6)=null, --指定null为默认值 @credit int = 3 --制定整数值3为默认值 as begin if @stuName=null begin print '没有指定学号，更新无法完成' return end update student set credit=credit+@credit where stuName=@stuName end exec increaseCredit '程琳',5 --提供所有参数 exec increaseCredit '程琳' --提供部分参数，则第二个参数使用默认值3 --指定参数方向 if exists(select name from sys.all_objects where name='avgCreditForMajor' and type='P') drop proc avgCreditForMajor create proc avgCreditForMajor @majorId int, --输入参数 @avg decimal(4,1) output --输出参数 as select @avg=avg(credit) from student where majorId=@majorId go declare @avgCredit decimal(4,1) exec avgCreditForMajor 1,@avgCredit output --将输出的返回值保存在@avgCredit变量中,注意输出参数也必须指定output关键字 print('专业平均学分：'+cast(@avgCredit as varchar)) --显示储存过程的返回值 go --使用return语句 use EBuy if exists(select name from sys.all_objects where name='checkCity' and type='P') drop proc avgCreditForMajor create proc checkCity @cusid varchar(20) as if(select address from customer where cusId=@cusid) like '%上海%' return 1 else if(select address from customer where cusId=@cusid) not like '%上海%'
３ return 2 else return 3 declare @ret_status int exec @ret_status=checkCity '1001' --客户住在上海 select @ret_status as '返回状态' declare @ret_status int exec @ret_status=checkCity '1004' --客户不住在上海 select @ret_status as '返回状态' declare @ret_status int exec @ret_status=checkCity '13204' --客户不存在的情况 select @ret_status as '返回状态' --事物和错误处理 create proc procTest as declare @num int select @num=9 begin try while @num>0 begin print convert(varchar,sqrt(@num)) select @num=@num-1 if @num=5 raiserror('基数已小于6',16,1) end end try begin catch print '放生错误的是：'+convert(varchar,error_procedure()) print '错误编号：'+convert(varchar,error_number()) print '错误描述：'+convert(varchar,error_message()) print '错误级别：'+convert(varchar,error_severity()) print '错误位于：'+convert(varchar,error_line()) print '错误状态：'+convert(varchar,error_state()) end catch go exec procTest --执行存储过程 --存储过程中的事务 use EBuy create table account( accId int, type varchar(10), balance decimal(10,2) )
４ insert into account values(33,'savings',3000) insert into account values(33,'checking',1500) if exists(select name from sys.all_objects where name='savingsToChecking' and type='P') drop proc savingsToChecking go create proc savingsToChecking @accid int, @num decimal(10,2) as declare @bal decimal(10,2) begin try select @bal=balance from account where accId=@accid and type='savings' if @num>@bal return 1 begin tran update account set balance=balance-@num where accId=@accid and type='savings' update account set balance=balance+@num where accId=@accid and type='checking' commit tran return 0 end try begin catch rollback tran return 2 end catch go declare @retCode int begin exec @retCode=savingsToChecking 33,1500 if @retCode=0 print '转账成功' else if @retCode=1 print '转账金额大于账面余额，转账失败' else print '数据库发生错误，转账失败' end --存储过程的查看、修改和删除 sp_helptext 'savingsToChecking' --查看存储过程 alter proc savingsToChecking @accid int, --此处可以修改参数的定义 @num decimal(10,2) drop proc savingsToChecking --删除存储过程 --重新编译存储过程 --1
５ sp_recompile savingsToChecking --2 create proc savingsToChecking @accid int, @num decimal(10,2) with recompile --创建存储过程的时候指定 as ...... --3 exec savingsToChecking 33,1500 with recompile