Create Proc [dbo].[Proc_AddEmail] @Sender char(36), --发件人 @Subject varchar(255),--邮件主题 @Addressee text,--收件人的工号/学号 @Message text, --邮件的内容 @IsPublic bit --邮件是否公共asDECLARE @ERROR INT --定义错误对象 SET @ERROR=0Begin Begin Tran declare @MailID char(36),@MailDate char(36)--邮件ID、当前日期声明 --邮件ID、当前日期赋值 set @MailID=NewID() set @MailDate=getdate() insert into dbo.Tbl_Mail(MailID,Subject,MailDate,FromID,[Message],IsPublic,RecordStatus) values(@MailID,@Subject,@MailDate,@Sender,@Message,@IsPublic,'A') SET @ERROR=@@error+@ERROR if @IsPublic=1 begin --插入学生用户 insert into Tbl_MailInbox(MailID,UserNO,UserType,ReCordStatus) select @MailID, studentID,'2','A' from Tbl_student where ReCordStatus='A' set @Error=@@error+@Error --插入老师用户 insert into Tbl_MailInbox(MailID,UserNO,UserType,ReCordStatus) select @MailID, TeacherID,'1','A' from Tbl_Teacher where ReCordStatus='A' set @Error=@@error+@Error end else begin --插入学生用户 insert into Tbl_MailInbox(MailID,UserNO,UserType,ReCordStatus) select @MailID, studentID,'2','A' from Tbl_student where ReCordStatus='A' and charindex(studentID,@Addressee)>0 set @Error=@@error+@Error --插入教师用户 insert into Tbl_MailInbox(MailID,UserNO,UserType,ReCordStatus) select @MailID, TeacherID,'2','A' from Tbl_Teacher where ReCordStatus='A' and charindex(TeacherID,@Addressee)>0 set @Error=@@error+@Error end if @ERROR=0 Commit ELSE ROLLBACK end
转载于:https://www.cnblogs.com/LYunF/archive/2012/10/22/2733987.html
