存储过程

今天第一次写一个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