CodeUSE [CoastalDB]
GO
/****** 对象: StoredProcedure [dbo].[Pro_EditUserInfo] 脚本日期: 08/07/2008 15:12:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-----------------------------------------------------
-- 名称：Pro_EditUser
-- 功能说明：新增/修改用户信息
-- 输入参数:
@Action "Insert" 为新增；"Update" 为更新；"Delete" 为删除
@UserName 用户帐号，删除时为以逗号分隔的多个'a','b'
@ActualName 姓名,
@Sex 性别,
@Age 年龄,
@Marriage 婚姻状况，
@Company 公司,
@Department 部门,
@Position 职位,
@CareerTime 本公司服务时间,
@Password 密码,
@UserRight 用户权限,
@Active 是否激活,
@Note 备注,
@FullName 中文,
-- 输出参数:
-- @errMsg 出错信息
-- 注意事项:
-- 原设计者: 薛云枫
-- 设计日期:2007/06/04
-- ------------------------ 变更纪录明细-------------
-- 变更日期 变更者 变 更 原 因
-------------------------------------------------------*/
ALTER procedure [dbo].[Pro_EditUserInfo]
( @Action nvarchar(20),
@UserName nvarchar(50),
@ActualName nvarchar(100),
@Sex nvarchar(50) ,
@Age nvarchar(100),
@Marriage nvarchar(50),
@Education nvarchar(100),
@Company nvarchar(500),
@CompanyProperty nvarchar(500),
@CompanyProfession nvarchar(500),
@EmployeeNum nvarchar(500),
@Department nvarchar(500),
@Position nvarchar(200),
@ManageNum nvarchar(500),
@WorkTime nvarchar(500),
@CareerTime nvarchar(200),
@Password nvarchar(50),
@UserRight nvarchar(50),
@Active nvarchar(50),
@Note nvarchar(200),
@errMsg varchar(100) output
)
as
begin
begin tran
if(@Action='Insert')
begin
insert into dbo.UserInfo(
UserName, ActualName, Sex,Age, Marriage,Education,Company,CompanyProperty, CompanyProfession,EmployeeNum, Department,
Position,ManageNum, WorkTime, CareerTime,Password,UserRight,Active,RegistTime,Note )
values (
@UserName,@ActualName, @Sex,@Age, @Marriage,@Education, @Company,@CompanyProperty ,@CompanyProfession , @EmployeeNum, @Department,
@Position,@ManageNum, @WorkTime,@CareerTime,@Password,@UserRight,@Active,getdate(),@Note );
if(@@error<>0)
begin
set @errMsg = '数据库执行出错!';
--set @errMsg = 'Database Execution Error Occured !';
goto Error_Handle ;
end
end
else if(@Action='Update')
begin
update dbo.UserInfo
set
ActualName=@ActualName,
Sex=@Sex,
Age=@Age,
Marriage=@Marriage ,
Education=@Education ,
Company=@Company ,
CompanyProperty = @CompanyProperty,
CompanyProfession = @CompanyProfession ,
EmployeeNum = @EmployeeNum ,
Department=@Department ,
Position=@Position ,
ManageNum = @ManageNum,
CareerTime =@CareerTime ,
Password =@Password ,
WorkTime = @WorkTime,
UserRight=@UserRight ,
Active=@Active ,
Note=@Note
where lower(UserName)=lower(@UserName);
if(@@error<>0)
begin
set @errMsg = '数据库执行出错!';
--set @errMsg = 'Database Execution Error Occured !';
goto Error_Handle ;
end
end
else if(@Action='Delete')
begin
if(right(@UserName,1)=',')
begin
set @UserName = @UserName+''''''; --随意补齐一个负数即可
end
declare @sql varchar(8000);
set @sql='delete from dbo.UserInfo
where lower(UserName) in('+lower(@UserName)+')';
execute (@sql);
if(@@error<>0)
begin
set @errMsg = '数据库执行出错!';
--set @errMsg = 'Database Execution Error Occured !';
goto Error_Handle ;
end
end
commit tran ;
return;
Error_Handle:
rollback tran;
return;
end
posted @ 2008-08-07 15:30 jantouy 阅读(451) 评论(4) 编辑