set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO create PROCEDURE [dbo].[STPGridList_InvoiceDetailChange] AS SELECT Users_1.UName AS OnChangeUName, ChangeinvoiceDetailTbl.OnChangeSysDate, ChangeinvoiceDetailTbl.OnChangeUserId, ChangeinvoiceDetailTbl.RowNo, ChangeinvoiceDetailTbl.Term, ChangeinvoiceDetailTbl.GoodSysCode, StockTbl.Title AS GoodTitle, StockTbl.Code, StockTbl.BarCode, GoodColorTbl.ColorName, WareHouseTbl.WareHouseName, GoodSizeTbl.SizeName, ChangeinvoiceDetailTbl.SysCode, ChangeinvoiceDetailTbl.DiscountPercent, ChangeinvoiceDetailTbl.FK_InvoiceSysCode, ChangeinvoiceDetailTbl.SerialNo, ChangeinvoiceDetailTbl.UnitRate, ChangeinvoiceDetailTbl.Unit, ChangeinvoiceDetailTbl.Fee, ChangeinvoiceDetailTbl.Quantity, ChangeinvoiceDetailTbl.Comment,cast((case when EXISTS (SELECT GoodSysCode FROM invoiceDetailTbl WHERE (GoodSysCode = ChangeinvoiceDetailTbl.GoodSysCode) AND (FK_InvoiceSysCode = ChangeinvoiceDetailTbl.FK_InvoiceSysCode) ) then 0 else 1 end ) as bit) as IsDelete FROM GoodColorTbl RIGHT OUTER JOIN ChangeinvoiceDetailTbl LEFT OUTER JOIN Users AS Users_1 ON ChangeinvoiceDetailTbl.OnChangeUserId = Users_1.UID LEFT OUTER JOIN GoodSizeTbl ON ChangeinvoiceDetailTbl.Size = GoodSizeTbl.SysCode LEFT OUTER JOIN WareHouseTbl ON ChangeinvoiceDetailTbl.WareHouse = WareHouseTbl.SysCode ON GoodColorTbl.SysCode = ChangeinvoiceDetailTbl.Color LEFT OUTER JOIN StockTbl ON ChangeinvoiceDetailTbl.GoodSysCode = StockTbl.GoodSyscode -- where ChangeinvoiceDetailTbl.goodsyscode not in (SELECT GoodSysCode FROM invoiceDetailTbl WHERE (GoodSysCode = ChangeinvoiceDetailTbl.GoodSysCode) AND (FK_InvoiceSysCode = ChangeinvoiceDetailTbl.FK_InvoiceSysCode)) ORDER BY ChangeinvoiceDetailTbl.FK_InvoiceSysCode, ChangeinvoiceDetailTbl.RowNo