初试SQL存储过程
USE [ReagentSystem] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= --CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> CREATE PROCEDURE [dbo].[AtRegularIntervalsResarch] AS Declare @COUNT INT --记录总数 Declare @I INT --控制循环 Declare @CreatedDate DateTime --货期时间 Declare @deltaT INT--时间差 Declare @CheckStatus varchar(200) --状态 NEW Declare @OrderId INT --订单ID Declare @CountDeliveryDateCheckNum INT --货期总数 Declare @CreatedDateTwo DateTime --货期时间第二次 Declare @deltaTTwo INT--时间差第二次 Declare @CreatedDateThree DateTime --货期时间第三次 Declare @deltaTThree INT--时间差第三次 Declare @UserComments varchar(200) Declare @SupplierName varchar(200) Declare @SupplierEmail varchar(200) Declare @AdminComments varchar(200) Declare @ProcessedDate varchar(200) Declare @SupplierCode varchar(200) Declare @MessageTitle varchar(200) Declare @MessageBody varchar(6000) Declare @SendToEmail varchar(200) Declare @SendFromEmail varchar(200) Declare @SendToName varchar(200) Declare @SendFromName varchar(200) Declare @MessageType varchar(200) Declare @MessageStatus varchar(200) Declare @RetryTimes INT Declare @ContactName varchar(200) Declare @ProductID INT Declare @ProductName varchar(200) Declare @ProductCompanyName varchar(200) Declare @NewDate varchar(200) SET @CheckStatus='NEW' SET @I=1 SET @COUNT = (SELECT Count(DeliveryCheckId) FROM tblDeliveryDateCheck WHERE CheckStatus='NEW') WHILE @I<=@COUNT BEGIN --获得OrderId SET @OrderId= (SELECT TOP 1 OrderId FROM tblDeliveryDateCheck WHERE CheckStatus=@CheckStatus AND DeliveryCheckId NOT IN(SELECT TOP (@I) DeliveryCheckId FROM tblDeliveryDateCheck WHERE CheckStatus=@CheckStatus)) SET @CountDeliveryDateCheckNum=(SELECT Count(DeliveryCheckId) FROM tblDeliveryDateCheck WHERE OrderId=@OrderId) if @CountDeliveryDateCheckNum=1 BEGIN --货期时间 SET @CreatedDate =(SELECT TOP 1 CreatedDate FROM tblDeliveryDateCheck WHERE CheckStatus=@CheckStatus AND DeliveryCheckId NOT IN(SELECT TOP (@I) DeliveryCheckId FROM tblDeliveryDateCheck WHERE CheckStatus=@CheckStatus)) --获取时间差 SET @deltaT=(select datediff(day,@CreatedDate,getdate())) if @deltaT>=30 BEGIN if @deltaT<=35 BEGIN SELECT @UserComments=UserComments,@SupplierName=SupplierName,@SupplierEmail=SupplierEmail,@AdminComments=AdminComments,@ProcessedDate=ProcessedDate,@SupplierCode=SupplierCode FROM tblDeliveryDateCheck WHERE OrderId=@OrderId SELECT @ContactName=ContactName,@ProductID=ProductID,@SupplierName=SupplierName,@SupplierEmail=SupplierEmail,@ProductName=ProductName,@ProductCompanyName=tblOrder.ProductCompanyName FROM tblDeliveryDateCheck,tblOrder WHERE tblDeliveryDateCheck.OrderId=tblOrder.OrderID AND tblDeliveryDateCheck.OrderId=@orderId SET @MessageTitle='生命科学研究院货期查询' SET @NewDate=(Select CONVERT(varchar(100), GETDATE(), 120)) SET @MessageBody=@SupplierName+'于'+convert(varchar(200),@NewDate)+'向贵公司已发货产品信息如下'+'<br/><br/>订单编号:'+convert(varchar(200),@orderId)+'<br/>品牌:'+convert(varchar(200),@ProductCompanyName)+'<br/>产品名称:'+@ProductName+'<br/>货号:'+convert(varchar(200),@ProductID)+'<br/><br/><br/>请在收到邮件后尽快通过邮件、电话、传真等方式回复采购部何时能够到货。如果回复不及时，有可能影响采购部下次的订购渠道选择，谢谢。' SET @SendToEmail=@SupplierEmail SET @SendFromEmail='11reagent@sibs.ac.cn' SET @SendToName=@SupplierName SET @SendFromName='11reagent@sibs.ac.cn' SET @MessageType='PLAIN_TXT' SET @RetryTimes=0 SET @MessageStatus=@CheckStatus --插入邮件数据 print(@MessageBody) print(@MessageTitle) print(@NewDate) print(@SendToEmail) print(@SendFromEmail) print(@SendToName) print(@SendFromName) print(@MessageType) print(@RetryTimes) --给供应商发送邮件 INSERT INTO tblMessage(MessageTitle,MessageBody,SendToEmail,SendFromEmail,SendToName,SendFromName,CreatedDate,MessageType,MessageStatus,RetryTimes,TemplateCode) VALUES(@MessageTitle,@MessageBody,@SendToEmail,@SendFromEmail,@SendToName,@SendFromName,getdate(),@MessageType,@MessageStatus,@RetryTimes,NULL) --ADMIN自己发送发送邮件 INSERT INTO tblMessage(MessageTitle,MessageBody,SendToEmail,SendFromEmail,SendToName,SendFromName,CreatedDate,MessageType,MessageStatus,RetryTimes,TemplateCode) VALUES(@MessageTitle,@MessageBody,@SendFromEmail,@SendFromEmail,@SendFromName,@SendFromName,getdate(),@MessageType,@MessageStatus,@RetryTimes,NULL) --添加一条货期查询数据 INSERT INTO tblDeliveryDateCheck(OrderId,UserComments,SupplierCode,SupplierName,SupplierEmail,CheckStatus,AdminComments,CreatedDate,ProcessedDate) VALUES(@OrderId,@UserComments,@SupplierCode,@SupplierName,@SupplierEmail,@CheckStatus,@AdminComments,getdate(),@ProcessedDate) END END END if @CountDeliveryDateCheckNum=2 BEGIN --货期时间 SET @CreatedDateTwo=(SELECT CreatedDate FROM tblDeliveryDateCheck WHERE DeliveryCheckId NOT IN(SELECT TOP 1 DeliveryCheckId FROM tblDeliveryDateCheck WHERE OrderId=@OrderId) AND OrderId=@OrderId) --获取时间差 SET @deltaTTwo=(select datediff(day,@CreatedDateTwo,getdate())) if @deltaTTwo>=10 BEGIN if @deltaTTwo<=15 BEGIN SELECT @UserComments=UserComments,@SupplierName=SupplierName,@SupplierEmail=SupplierEmail,@AdminComments=AdminComments,@ProcessedDate=ProcessedDate,@SupplierCode=SupplierCode FROM tblDeliveryDateCheck WHERE OrderId=@OrderId SELECT @ContactName=ContactName,@ProductID=ProductID,@SupplierName=SupplierName,@SupplierEmail=SupplierEmail,@ProductName=ProductName,@ProductCompanyName=tblOrder.ProductCompanyName FROM tblDeliveryDateCheck,tblOrder WHERE tblDeliveryDateCheck.OrderId=tblOrder.OrderID AND tblDeliveryDateCheck.OrderId=@orderId SET @MessageTitle='生命科学研究院货期查询' SET @NewDate=(Select CONVERT(varchar(100), GETDATE(), 120)) SET @MessageBody=@SupplierName+'于'+convert(varchar(200),@NewDate)+'向贵公司已发货产品信息如下'+'<br/><br/>订单编号:'+convert(varchar(200),@orderId)+'<br/>品牌:'+convert(varchar(200),@ProductCompanyName)+'<br/>产品名称:'+@ProductName+'<br/>货号:'+convert(varchar(200),@ProductID)+'<br/><br/><br/>请在收到邮件后尽快通过邮件、电话、传真等方式回复采购部何时能够到货。如果回复不及时，有可能影响采购部下次的订购渠道选择，谢谢。' SET @SendToEmail=@SupplierEmail SET @SendFromEmail='11reagent@sibs.ac.cn' SET @SendToName=@SupplierName SET @SendFromName='11reagent@sibs.ac.cn' SET @MessageType='PLAIN_TXT' SET @RetryTimes=0 SET @MessageStatus=@CheckStatus --插入邮件数据 print(@MessageBody) print(@MessageTitle) print(@NewDate) print(@SendToEmail) print(@SendFromEmail) print(@SendToName) print(@SendFromName) print(@MessageType) print(@RetryTimes) --给供应商发送邮件 INSERT INTO tblMessage(MessageTitle,MessageBody,SendToEmail,SendFromEmail,SendToName,SendFromName,CreatedDate,MessageType,MessageStatus,RetryTimes,TemplateCode) VALUES(@MessageTitle,@MessageBody,@SendToEmail,@SendFromEmail,@SendToName,@SendFromName,getdate(),@MessageType,@MessageStatus,@RetryTimes,NULL) --ADMIN自己发送发送邮件 INSERT INTO tblMessage(MessageTitle,MessageBody,SendToEmail,SendFromEmail,SendToName,SendFromName,CreatedDate,MessageType,MessageStatus,RetryTimes,TemplateCode) VALUES(@MessageTitle,@MessageBody,@SendFromEmail,@SendFromEmail,@SendFromName,@SendFromName,getdate(),@MessageType,@MessageStatus,@RetryTimes,NULL) --添加一条货期查询数据 INSERT INTO tblDeliveryDateCheck(OrderId,UserComments,SupplierCode,SupplierName,SupplierEmail,CheckStatus,AdminComments,CreatedDate,ProcessedDate) VALUES(@OrderId,@UserComments,@SupplierCode,@SupplierName,@SupplierEmail,@CheckStatus,@AdminComments,getdate(),@ProcessedDate) END END END if @CountDeliveryDateCheckNum=3 BEGIN --货期时间 SET @CreatedDateThree=(SELECT CreatedDate FROM tblDeliveryDateCheck WHERE DeliveryCheckId NOT IN(SELECT TOP 2 DeliveryCheckId FROM tblDeliveryDateCheck WHERE OrderId=@OrderId) AND OrderId=@OrderId) --获取时间差 SET @deltaTThree=(select datediff(day,@CreatedDateThree,getdate())) if @deltaTThree>=10 BEGIN if @deltaTThree<=15 BEGIN SELECT @UserComments=UserComments,@SupplierName=SupplierName,@SupplierEmail=SupplierEmail,@AdminComments=AdminComments,@ProcessedDate=ProcessedDate,@SupplierCode=SupplierCode FROM tblDeliveryDateCheck WHERE OrderId=@OrderId SELECT @ContactName=ContactName,@ProductID=ProductID,@SupplierName=SupplierName,@SupplierEmail=SupplierEmail,@ProductName=ProductName,@ProductCompanyName=tblOrder.ProductCompanyName FROM tblDeliveryDateCheck,tblOrder WHERE tblDeliveryDateCheck.OrderId=tblOrder.OrderID AND tblDeliveryDateCheck.OrderId=@orderId SET @MessageTitle='生命科学研究院货期查询' SET @NewDate=(Select CONVERT(varchar(100), GETDATE(), 120)) SET @MessageBody=@SupplierName+'于'+convert(varchar(200),@NewDate)+'向贵公司已发货产品信息如下'+'<br/><br/>订单编号:'+convert(varchar(200),@orderId)+'<br/>品牌:'+convert(varchar(200),@ProductCompanyName)+'<br/>产品名称:'+@ProductName+'<br/>货号:'+convert(varchar(200),@ProductID)+'<br/><br/><br/>请在收到邮件后尽快通过邮件、电话、传真等方式回复采购部何时能够到货。如果回复不及时，有可能影响采购部下次的订购渠道选择，谢谢。' SET @SendToEmail=@SupplierEmail SET @SendFromEmail='11reagent@sibs.ac.cn' SET @SendToName=@SupplierName SET @SendFromName='11reagent@sibs.ac.cn' SET @MessageType='PLAIN_TXT' SET @RetryTimes=0 SET @MessageStatus=@CheckStatus --插入邮件数据 print(@MessageBody) print(@MessageTitle) print(@NewDate) print(@SendToEmail) print(@SendFromEmail) print(@SendToName) print(@SendFromName) print(@MessageType) print(@RetryTimes) --给供应商发送邮件 INSERT INTO tblMessage(MessageTitle,MessageBody,SendToEmail,SendFromEmail,SendToName,SendFromName,CreatedDate,MessageType,MessageStatus,RetryTimes,TemplateCode) VALUES(@MessageTitle,@MessageBody,@SendToEmail,@SendFromEmail,@SendToName,@SendFromName,getdate(),@MessageType,@MessageStatus,@RetryTimes,NULL) --ADMIN自己发送发送邮件 INSERT INTO tblMessage(MessageTitle,MessageBody,SendToEmail,SendFromEmail,SendToName,SendFromName,CreatedDate,MessageType,MessageStatus,RetryTimes,TemplateCode) VALUES(@MessageTitle,@MessageBody,@SendFromEmail,@SendFromEmail,@SendFromName,@SendFromName,getdate(),@MessageType,@MessageStatus,@RetryTimes,NULL) --添加一条货期查询数据 INSERT INTO tblDeliveryDateCheck(OrderId,UserComments,SupplierCode,SupplierName,SupplierEmail,CheckStatus,AdminComments,CreatedDate,ProcessedDate) VALUES(@OrderId,@UserComments,@SupplierCode,@SupplierName,@SupplierEmail,@CheckStatus,@AdminComments,getdate(),@ProcessedDate) END END END SET @I=@I+1 END GO
在ASP.NET后台调用
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]); conn.Open(); SqlCommand cmd = new SqlCommand("AtRegularIntervalsResarch", conn); cmd.CommandType = CommandType.StoredProcedure;//设置cmd的类型为存储过程 cmd.ExecuteNonQuery(); if (conn.State == ConnectionState.Open) { conn.Close(); }
