容桂低价网站建设,网站后台路径,广州icp网站测评,国家公示企业信息查询今天写几个存储过程#xff0c;觉得有这个必要记录下来#xff0c;方便以后忘了也好有个备份#xff0c;都很简单#xff0c;高手可以不用看的。一、记录的插入--region [dbo].[InsertArchive]--------------------------------------------------------------------------…今天写几个存储过程觉得有这个必要记录下来方便以后忘了也好有个备份都很简单高手可以不用看的。 一、记录的插入--region [dbo].[InsertArchive]-------------------------------------------------------------------------------------------------------------------------- Generated By: wangzeng using CodeSmith 4.0.0.0-- Template: StoredProcedures.cst-- Procedure Name: [dbo].[InsertArchive]-- Date Generated: 2007年11月28日--------------------------------------------------------------------------------------------------------------------------插入档案记录ALTER PROCEDURE [dbo].[InsertArchive] Name varchar(50), Sex int, PostID int, OrgID int, WorkTypeID varchar(20), ArchivesID int OUTPUTAS--SET NOCOUNT ONINSERT INTO [dbo].[Archives] ( [Name], [Sex], [PostID], [OrgID], [WorkTypeID]) VALUES ( Name, Sex, PostID, OrgID, WorkTypeID)SET ArchivesID SCOPE_IDENTITY()--endregion 二、动态查询这个是由codesmit生成的然后稍微改了点条件可以任意组合--动态查询定单ALTER PROCEDURE [dbo].[SelectOrdersDynamic] WhereCondition nvarchar(500), OrderByExpression nvarchar(250) NULLASSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ COMMITTEDDECLARE SQL nvarchar(3250)SET SQL SELECT [ID], [OrderID], Orders.SupplierCode, SupplierName, AddDatecase when Orders.AddDate1900-1-1 then convert(nvarchar,year(Orders.AddDate))-convert(nvarchar,month(Orders.AddDate))-convert(nvarchar,day(Orders.AddDate)) else end, ClaimDatecase when ClaimDate1900-1-1 then convert(nvarchar,year(ClaimDate))-convert(nvarchar,month(ClaimDate))-convert(nvarchar,day(ClaimDate)) else end, FactDatecase when FactDate1900-1-1 then convert(nvarchar,year(FactDate))-convert(nvarchar,month(FactDate))-convert(nvarchar,day(FactDate)) else end, [Lister], [Assessor], [Validate], [Auditing], [Perform], [IsClose], ValidateStatecase when Validate1 then 是 when Validate0 then 否 end, AuditingStatecase when Auditing1 then 是 when Auditing0 then 否 end, PerformStatecase when Perform0 then 未执行 when Perform1 then 执行中 when Perform2 then 执行完成 end, IsCloseStatecase when IsClose1 then 关闭 when IsClose0 then 正常 end, ValidateUrlcase when Validate0 then OrderDetail.aspx?OrderIDOrderID else end, ValidateCsscase when Validate0 then bluelink else nolink end, ShipmentUrlcase when Perform1 then Shipment.aspx?OrderIDOrderID else end, ShipmentCsscase when Perform1 then bluelink else nolink end, SupplierName, 0 as TotalMaterialNum, 0 as LackMaterialNum, 0.0 as TotalMoney, PayMode, Remark, Orders.OtherValueFROM Orders left join Supplier on Orders.SupplierCodeSupplier.SupplierCodeWHERE WhereConditionIF OrderByExpression IS NOT NULL AND LEN(OrderByExpression) 0BEGIN SET SQL SQL ORDER BY ID desc--OrderByExpressionENDEXEC sp_executesql SQL 三、修改记录这个存储过程这么写主要是为了修改的时候方便因为如果你不要修改的地方就不要传参数传统的存储过程是把原来的参数重传一次这个灵活点。--修改定单信息ALTER PROCEDURE [dbo].[UpdateOrder] OrderID varchar(200), SupplierCode varchar(200), AddDate datetime, ClaimDate datetime, FactDate datetime, Lister varchar(50), Assessor varchar(50), Validate int, Auditing int, Perform int, IsClose int, PayMode varchar(50), Remark nvarchar(500), OtherValue varchar(50)AS--SET NOCOUNT ONdeclare SQL nvarchar(1000)set SQLUPDATE Orders if(len(SupplierCode)0)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,SupplierCodeconvert(varchar(200),SupplierCode) end else begin set SQLSQL set SupplierCodeconvert(varchar(200),SupplierCode) endendif(AddDate1900-1-1)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,AddDateconvert(varchar,AddDate) end else begin set SQLSQL set AddDateconvert(varchar,AddDate) endendif(ClaimDate1900-1-1)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,ClaimDateconvert(varchar,ClaimDate) end else begin set SQLSQL set ClaimDateconvert(varchar,ClaimDate) endendif(FactDate1900-1-1)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,FactDateconvert(varchar,FactDate) end else begin set SQLSQL set FactDateconvert(varchar,FactDate) endendif(len(Lister)0)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,Listerconvert(varchar,Lister) end else begin set SQLSQL set Listerconvert(varchar,Lister) endendif(len(Assessor)0)begin if(len(SQL)len(UPDATE Orders set )) begin if Assessornull begin set Assessor end set SQLSQL,Assessorconvert(varchar,Assessor) end else begin if Assessornull begin set Assessor end set SQLSQL set Assessorconvert(varchar,Assessor) endendif(cast(Validate as int)-1)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,Validateconvert(varchar,Validate) end else begin set SQLSQL set Validateconvert(varchar,Validate) endendif(cast(Auditing as int)-1)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,Auditingconvert(varchar,Auditing) end else begin set SQLSQL set Auditingconvert(varchar,Auditing) endendif(cast(Perform as int)-1)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,Performconvert(varchar,Perform) end else begin set SQLSQL set Performconvert(varchar,Perform) endendif(cast(IsClose as int)-1)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,IsCloseconvert(varchar,IsClose) end else begin set SQLSQL set IsCloseconvert(varchar,IsClose) endendif(len(PayMode)0)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,PayModeconvert(varchar,PayMode) end else begin set SQLSQL set PayModeconvert(varchar,PayMode) endendif(len(convert(nvarchar,Remark))0)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,Remarkconvert(nvarchar(500),Remark) end else begin set SQLSQL set Remarkconvert(nvarchar(500),Remark) end --update Orders set RemarkRemark where OrderIDOrderIDendif(len(OtherValue)0)begin if(len(SQL)len(UPDATE Orders set )) begin set SQLSQL,OtherValueconvert(varchar,OtherValue) end else begin set SQLSQL set OtherValueconvert(varchar,OtherValue) endendset SQLSQL where OrderIDconvert(nvarchar(200),OrderID)print SQLexec(SQL)if(len(SupplierCode)0)begin update Supplier set LastUseDategetdate() where SupplierCodeSupplierCode --没有统计修改的次数因为修改时经常是同一个供应商end/**//*UPDATE [dbo].[Orders] SET [SupplierCode] SupplierCode, [AddDate] AddDate, [ClaimDate]ClaimDate, [FactDate] FactDate, [Lister] Lister, [Assessor] Assessor, [Validate] Validate, [Auditing] Auditing, [Perform] PerformWHERE [OrderID] OrderID*/ 转载于:https://www.cnblogs.com/ringwang/archive/2007/12/14/994828.html
