set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO create PROCEDURE [dbo].[STPGoodProperty] @SysCode int AS SELECT StockTbl.Comment, VWGoodSimpleLastSummary.LastDate, VWGoodSimpleLastSummary.LastSeller, VWGoodSimpleLastSummary.LastFee, (CASE StockTbl.UnitRate WHEN 1 THEN NULL ELSE CAST(floor(VWGoodSimpleRemain.Remain / StockTbl.UnitRate) AS nvarchar) + N' : ' + CAST(VWGoodSimpleRemain.Remain - floor(VWGoodSimpleRemain.Remain / StockTbl.UnitRate) * StockTbl.UnitRate AS nvarchar) END) AS Remain, StockTbl.VisitorPer, StockTbl.UserPrice, stockGroup1Tbl.GroupName AS GroupName1, stockGroup2Tbl.GroupName AS GroupName2, StockTbl.Weight, StockTbl.Term FROM StockTbl INNER JOIN stockGroup1Tbl ON StockTbl.GroupID1 = stockGroup1Tbl.GroupId INNER JOIN stockGroup2Tbl ON StockTbl.GroupID2 = stockGroup2Tbl.GroupId LEFT OUTER JOIN VWGoodSimpleLastSummary ON StockTbl.GoodSyscode = VWGoodSimpleLastSummary.GoodSysCode LEFT OUTER JOIN VWGoodSimpleRemain ON StockTbl.GoodSyscode = VWGoodSimpleRemain.GoodSysCode WHERE (StockTbl.GoodSyscode = @syscode)