今天第一次写一个100行的存储过程。。。 贴出来请专家点评一下~。。写得不好，不要介意哈。。。第一次写~。。 USE [Test]GO/****** 对象: StoredProcedure [dbo].[UpdateSalesPrice1] 脚本日期: 07/09/2008 09:29:30 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[UpdateSalesPrice1]
( @salesPrice float, -- this is the new salesprice.
@salesPriceFormula varchar(255), --this is the new formula
@customerID varchar(50), --@purchaseID int,
@productVariantID int,
@updateDate datetime,
@priceLevelID varchar(50),
@priceRemarks varchar(255),
@newcurrency varchar(50),
@validateEndDate datetime
--@oldCurrency varchar(50),
--@newSalesPriceFormula varchar(255)--@newSalesPrice float, )
AS if exists ( select SalesPrice from tblcustomerOrderlist A LEFT JOIN tblproductsupplier B ON A.purchaseId=B.purchaseID LEFT JOIN tblStatus C on A.statusID=C.StatusID WHERE CustomerID=@customerID AND productVariantID=@productVariantID AND CustomerID=@priceLevelID and inactive=0)
begin
if @priceLevelID= (select customerId from tblcustomerOrderlist A LEFT JOIN tblproductsupplier B ON A.purchaseId=B.purchaseID LEFT JOIN tblStatus C on A.statusID=C.StatusID WHERE CustomerID=@customerID AND productVariantID=@productVariantID AND CustomerID=@priceLevelID and inactive=0 )
begin UPDATE tblCustomerOrderList SET oldSalesprice=SalesPrice, OldSalepriceCurrency=currency, oldSalesformula=SalesPriceFormula , [currency]=@newcurrency, SalesPrice=@salesPrice, SalesPriceFormula=Replace(@salesPriceFormula,customerId,isnull(SalesPriceFormula,customerID)), PriceLastUpdateDate=@updateDate, UpdateDate=@updateDate, PriceRemarks=@priceRemarks FROM tblcustomerOrderlist A LEFT JOIN tblproductsupplier B ON A.purchaseId=B.purchaseID LEFT JOIN tblStatus C on A.statusID=C.StatusID WHERE CustomerID=@customerID AND productVariantID=@productVariantID AND CustomerID=@priceLevelID and inactive=0 end else begin UPDATE tblCustomerOrderList SET oldSalesprice=SalesPrice, OldSalepriceCurrency=currency, oldSalesformula=SalesPriceFormula , [currency]=@newcurrency, SalesPrice=@salesPrice, SalesPriceFormula=Replace(@salesPriceFormula,customerId,isnull(SalesPriceFormula,customerID)), PriceLastUpdateDate=@updateDate, UpdateDate=@updateDate, PriceRemarks=@priceRemarks FROM tblcustomerOrderlist A LEFT JOIN tblproductsupplier B ON A.purchaseId=B.purchaseID LEFT JOIN tblStatus C on A.statusID=C.StatusID WHERE CustomerID=@customerID AND productVariantID=@productVariantID AND CustomerID=@priceLevelID and inactive=0
end endelse
begin UPDATE tblCustomerOrderList SET [currency]=@newcurrency, SalesPrice=@salesPrice, SalesPriceFormula=@salesPriceFormula, PriceLastUpdateDate=@updateDate, UpdateDate=@updateDate, PriceRemarks=@priceRemarks FROM tblcustomerOrderlist A LEFT JOIN tblproductsupplier B ON A.purchaseId=B.purchaseID LEFT JOIN tblStatus C on A.statusID=C.StatusID WHERE CustomerID=@customerID AND productVariantID=@productVariantID AND CustomerID=@priceLevelID and inactive=0 endRETURN
发表于 @ 2008年07月09日 09:59:56|评论(loading...)|编辑|收藏