--====================================================================================
-- 修改存储过程dt_EnterprisePhoneBookAdd(添加联系人)
-- 联系人列表添加了职务列，所有添加联系人信息的存储过程要做相应修改
-- [email protected]
[email protected]=====================
--========================dt_EnterprisePhoneBookAdd===================================
/****** Object: Stored Procedure dbo.dt_EnterprisePhoneBookAdd Script Date: 2006-12-26 11:15:38 ******/
CREATE PROCEDURE dbo.dt_EnterprisePhoneBookAdd
@OwnerId int,
@Name varchar(100),
@Duty varchar(100),
@Sex tinyint,
@langchoice tinyint,
@OnDutyPhone varchar(21),
@DeptId int,
@Description varchar(100),
@Email varchar(100),
@ContactId int Output,
@Status int Output
AS
--------------------------------------------------
--Author : Ira
--------------------------------------------------
declare @factnum int,@maxrec int,@temp int
declare @memberaccount varchar(11),@TempAccount int
declare @StaffCOS int
begin transaction
select @ContactId=0
select @factnum=count(*) from tab_EnterprisePhoneBook
where [email protected]
select @maxrec=MaxPhoneRec from tab_EnterpriseUser a,tab_MaxPhoneRec b where a.MaxRecId=b.MaxRecId and [email protected]
select @[email protected]
if @factnum>[email protected] --超过权限
begin
select @status=8 --超过了能够存储电话簿数量的范
rollback transaction
return
end
select @StaffCOS=StaffCOS from tab_CosTemplateOne where [email protected]
--生成企业个人帐号
select @TempAccount=ISNULL(max(CONVERT(numeric,Account)),0) from tab_EnterprisePhoneBook where [email protected]
select @MemberAccount=CONVERT(varchar(11),@TempAccount)
select @MemberAccount=CONVERT(varchar(4), CONVERT(numeric,RIGHT(@MemberAccount,4))+1)
while len(@MemberAccount)<4
begin
Set @MemberAccount='0' + @MemberAccount
end
set @MemberAccount = CONVERT(varchar(4),@OwnerId) + @MemberAccount
insert into tab_EnterprisePhoneBook(
OwnerId,
Account,
[Password],
[Name],
Duty,
OnDutyPhone,
Sex,
LangChoice,
DeptId,
StaffCOS,
EmailAddress,
Memo
)
values(
@OwnerId,
@MemberAccount,
'123456',
@Name,
@Duty,
@OnDutyPhone,
@Sex,
@langchoice,
@DeptId,
@StaffCOS,
@Email,
@Description)
if @@error<>0
begin
select @status=1
return
end
select @ContactId=max(ContactId) from tab_EnterprisePhoneBook
select @status=0
commit transaction
return
GO
请高手帮我用mysql语句写，不需要写成存储过程了。谢谢。
------解决方案--------------------
建议去MYSQL版问问.