存储过程用例--新增、修改、删除数据
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo/******************************************************* PROCEDURE : pro_set_so_cust_info** DECRIPTION: 维护客户资料信息** DATE : 2012-07-14** VERSION AUTH DATE Defect NoDESC** -------- ------------ ------------ ----------------- ------------------------------** V000.0.1 pukuimin 2012-07-14新建程序** -------- ------------ ------------ ----------------- -------------------------------*******************************************************/ALTER procedure [dbo].[pro_set_so_cust_info]( @opr_typ int , -- 1：新增 2: 修改 3：删除@CUST_CD 编号, --客户编号@CUST_NAM varchar(200), --客户名称@ret varchar(20) output-- 0：成功， 其他：失败 )asbegindeclare @cust_cd2 varchar(20),@cur_date datetimeset @cur_date = getdate()begin try--if @opr_typ = 1 -- 新增begin-- 获取编码exec dbo.pro_sys_generate_code 'KH', @cur_date,@cust_cd2 output --调用其它存储过程，返回编码INSERT INTO t_so_cust_info( cust_cd, cust_nam, )VALUES( @cust_cd2, @cust_nam, )SELECT @ret = max(id) FROM T_SO_CUST_INFOend elseif @opr_typ = 2 -- 修改,将之前的状态修改为0,然后新增beginSELECT @create_usr_id = create_usr_id , @cur_date = create_dtimFROM t_so_cust_info WHERE id = @id update t_so_cust_info set stat = 0 where id = @idINSERT INTO t_so_cust_info( cust_cd, cust_nam, )select @cust_cd, @cust_nam,from t_so_cust_info where id = @id SELECT @ret = max(id) FROM T_SO_CUST_INFOend else if @opr_typ = 3 -- 删除,将状态修改为0,表示不可用,不对数据库做物理删除beginupdate t_so_cust_info set stat = 0 ,LAST_UPDT_USR_ID = @LAST_UPDT_USR_ID,LAST_UPDT_DTIM = GETDATE(),STAT_DTIM = GETDATE()where id = @idset @ret = 0endend trybegin catchset @ret = 'error:'+cast(ERROR_NUMBER() AS varchar(20))end catchend/*测试declare @ret varchar(20)exec pro_set_so_cust_info 1 , -- 1：新增 2: 修改 3：删除'123231', --客户编号@ret output-- 0：成功， 其他：失败 print @ret*/
