存储过程用例--新增、修改、删除数据

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*/