/****** Object:  StoredProcedure [dbo].[up_find_spare]    Script Date: 12/08/2011 14:47:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        prcak47    
-- Create date: 20111200
-- Description:    分别有库存和仓库二张表，现在给定缺少的备件名称，缺货的仓库名称，以及缺货的数量，
--                找出拥有这个备件的离缺货仓库最近的仓库名称，
-- =============================================
create PROCEDURE [dbo].[up_find_spare] @SpareName varchar(8),@StorageName varchar(8),@SpareAmount int

AS
BEGIN

----找到存货数量大于需求数量的仓库
select storagename,stockamount
into #stock_has
from stock
    where SpareName = @SpareName
    and stockamount >= @SpareAmount

----找到有配件的,距离最短的仓库,但是算法上只能是各个仓库距距离中心的位置和
----这会造成如果仓库本身有库存,距离是仓库本身距离中心的两倍
select top 1 storage.storageName,
        #stock_has.stockamount,
        storage.distance + storage_own.distance
from storage,#stock_has,storage as storage_own
    where storage.storageName = #stock_has.storageName
    and storage_own.StorageName = @StorageName
    order by storage.distance + storage_own.distance desc
    
END