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