IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='實地盤點_PROC' AND TYPE='P')
DROP PROC 實地盤點_PROC
GO
create procedure  實地盤點_PROC
@item_to varchar(30),
@item_fr varchar(30),
@TYPE CHAR(1)
as
CREATE TABLE [#實地盤點數據表] 
        (
	[物料編碼] [varchar] (30) ,
	[倉庫編碼] [varchar] (10) ,
	[貨位] [varchar] (10) ,
	[批號] [varchar] (15)  ,
	[狀態] [char] (1)  ,
	[截止數量] [numeric](18, 6) DEFAULT (0),
	[實際數量] [numeric](18, 0)  DEFAULT (0),
	[數量差異] AS ([實際數量] - [截止數量]) ,
	[材料成本金額] [numeric](18, 6)  DEFAULT (0),
	[人工成本金額] [numeric](18, 6) DEFAULT (0),
	[制造費用金額] [numeric](18, 6)  DEFAULT (0))
	
	
insert into #實地盤點數據表( 物料編碼,倉庫編碼,貨位,批號,狀態,截止數量,實際數量,材料成本金額,人工成本金額,制造費用金額)
SELECT   物料編碼,倉庫編碼,貨位,批號,狀態,截止數量,實際數量,材料成本金額,人工成本金額,制造費用金額from 實地盤點數據表  where 物料編碼>=@ITEM_TO AND  物料編碼<=@ITEM_FR
IF @TYPE ='C'
SELECT* FROM #實地盤點數據表
IF @TYPE ='N'
SELECT 物料編碼,倉庫編碼,貨位,批號,狀態,截止數量,實際數量, 數量差異 FROM #實地盤點數據表