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

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



/*****************************************************
** PROCEDURE : pro_set_so_cust_info
** DECRIPTION: 维护客户资料信息
** DATE      : 2012-07-14
** VERSION      AUTH          DATE          Defect No			DESC
** --------  ------------  ------------  -----------------   ------------------------------
** 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：成功， 其他：失败 
								 )as
begin
	declare @cust_cd2 varchar(20),
			@cur_date datetime
	set @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_INFO
			
		end else
		if @opr_typ = 2 -- 修改,将之前的状态修改为0,然后新增
		begin
			SELECT @create_usr_id = create_usr_id , @cur_date = create_dtim
				FROM t_so_cust_info WHERE id = @id 
			update t_so_cust_info set stat = 0 where id = @id
			INSERT 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_INFO
		end else 
		if @opr_typ = 3 -- 删除,将状态修改为0,表示不可用,不对数据库做物理删除
		begin				
				update t_so_cust_info set stat = 0 
								,LAST_UPDT_USR_ID = @LAST_UPDT_USR_ID
								,LAST_UPDT_DTIM = GETDATE()
								,STAT_DTIM = GETDATE()
				where id = @id
				
			set @ret = 0
		end
	end try
	begin catch
		set @ret = 'error:'+cast(ERROR_NUMBER() AS varchar(20))
	end catch
end


/*			测试
declare @ret varchar(20)
exec pro_set_so_cust_info           1 , -- 1：新增 2: 修改 3：删除
									'123231', --客户编号
									@ret output-- 0：成功， 其他：失败 
print @ret
*/