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)