使用存储过程中的虚拟表解决同时从几个数据库服务器中读取记录的问题
下面的存储过程语句中使用到了：
1) 在select后使用ISNULL(列名,0)，将该列中的Null值转化为0 。
2) 使用 If exists()判断查询是否存在记录。
3) 使用select语句给update 的set 语句赋值。
USE [u8m_sh]
GO
/****** Object: StoredProcedure [dbo].[getstock_1] Script Date: 01/20/2010 09:30:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[getstock_1]
-- Add the parameters for the stored procedure here
@part varchar(50)
AS
BEGIN
--select @part='20646694A'
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--need if exists(select part,description,safqty from u8m_wx.dbo.inv10100 where part=@part )
--need begin
-- Insert statements for procedure here
create table #t_total(part varchar(50),onhand_s001 numeric(14,4) NULL,onorder_s001 numeric(14,4) NULL,allocate_s001 numeric(14,4) NULL,onhand_s004 numeric(14,4) NULL,onorder_s004 numeric(14,4) NULL,allocate_s004 numeric(14,4) NULL,onhand_s007 numeric(14,4) NULL,onorder_s007 numeric(14,4) NULL,allocate_s007 numeric(14,4) NULL,onhand_s201 numeric(14,4) NULL,onorder_s201 numeric(14,4) NULL,allocate_s201 numeric(14,4) NULL,onhand_s202 numeric(14,4) NULL,onorder_s202 numeric(14,4) NULL,allocate_s202 numeric(14,4) NULL,
onhand_w007 numeric(14,4) NULL,onorder_w007 numeric(14,4) null,allocate_w007 numeric(14,4) NULL,onhand_w201 numeric(14,4) null,onorder_w201 numeric(14,4) NULL,allocate_w201 numeric(14,4) NULL,onhand_w202 numeric(14,4) null,onorder_w202 numeric(14,4) NULL,allocate_w202 numeric(14,4) NULL,onhand_w204 numeric(14,4) null,onorder_w204 numeric(14,4) NULL,allocate_w204 numeric(14,4) NULL,onhand_w308 numeric(14,4) NULL,onorder_w308 numeric(14,4) NULL,allocate_w308 numeric(14,4) NULL,onhand_t002 numeric(14,4) NULL,onorder_t002 numeric(14,4) NULL,allocate_t002 numeric(14,4) NULL)
insert into #t_total
(part)
values
(@part)
if exists(select * from u8m_sh.dbo.inv10104 where part=@part and prloc='001')
begin
update #t_total
set onhand_s001=(select isnull(onhand,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='001' ),
onorder_s001=(select isnull(onorder,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='001' ),
allocate_s001=(select isnull(allocate,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='001' )
end
else
begin
update #t_total
set onhand_s001=0,
onorder_s001=0,
allocate_s001=0
end
if exists(select * from u8m_sh.dbo.inv10104 where part=@part and prloc='004')
begin
update #t_total
set onhand_s004=(select isnull(onhand,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='004' ),
onorder_s004=(select isnull(onorder,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='004' ),
allocate_s004=(select isnull(allocate,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='004' )
end
else
begin
update #t_total
set onhand_s004=0,
onorder_s004=0,
allocate_s004=0
end
if exists(select * from u8m_sh.dbo.inv10104 where part=@part and prloc='007')
begin
update #t_total
set onhand_s007=(select isnull(onhand,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='007' ),
onorder_s007=(select isnull(onorder,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='007' ),
allocate_s007=(select isnull(allocate,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='007' )
end
else
begin
update #t_total
set onhand_s007=0,
onorder_s007=0,
allocate_s007=0
end
if exists(select * from u8m_sh.dbo.inv10104 where part=@part and prloc='201')
begin
update #t_total
set onhand_s201=(select isnull(onhand,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='201' ),
onorder_s201=(select isnull(onorder,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='201' ),
allocate_s201=(select isnull(allocate,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='201' )
end
else
begin
update #t_total
set onhand_s201=0,
onorder_s201=0,
allocate_s201=0
end
if exists(select * from u8m_sh.dbo.inv10104 where part=@part and prloc='202')
begin
update #t_total
set onhand_s202=(select isnull(onhand,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='202' ),
onorder_s202=(select isnull(onorder,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='202' ),
allocate_s202=(select isnull(allocate,0) from u8m_sh.dbo.inv10104 where part=@part and prloc='202' )
end
else
begin
update #t_total
set onhand_s202=0,
onorder_s202=0,
allocate_s202=0
end
if exists(select * from u8m_wx.dbo.inv10104 where part=@part and prloc='007')
begin
update #t_total
set onhand_w007=(select isnull(onhand,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='007' ),
onorder_w007=(select isnull(onorder,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='007' ),
allocate_w007=(select isnull(allocate,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='007' )
end
else
begin
update #t_total
set onhand_w007=0,
onorder_w007=0,
allocate_w007=0
end
if exists(select * from u8m_wx.dbo.inv10104 where part=@part and prloc='201')
begin
update #t_total
set onhand_w201=(select isnull(onhand,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='201' ),
onorder_w201=(select isnull(onorder,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='201' ),
allocate_w201=(select isnull(allocate,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='201' )
end
else
begin
update #t_total
set onhand_w201=0,
onorder_w201=0,
allocate_w201=0
end
if exists(select * from u8m_wx.dbo.inv10104 where part=@part and prloc='202')
begin
update #t_total
set onhand_w202=(select isnull(onhand,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='202' ),
onorder_w202=(select isnull(onorder,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='202' ),
allocate_w202=(select isnull(allocate,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='202' )
end
else
begin
update #t_total
set onhand_w202=0,
onorder_w202=0,
allocate_w202=0
end
if exists(select * from u8m_wx.dbo.inv10104 where part=@part and prloc='204')
begin
update #t_total
set onhand_w204=(select isnull(onhand,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='204' ),
onorder_w204=(select isnull(onorder,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='204' ),
allocate_w204=(select isnull(allocate,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='204' )
end
else
begin
update #t_total
set onhand_w204=0,
onorder_w204=0,
allocate_w204=0
end
if exists(select * from u8m_wx.dbo.inv10104 where part=@part and prloc='308')
begin
update #t_total
set onhand_w308=(select isnull(onhand,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='308' ),
onorder_w308=(select isnull(onorder,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='308' ),
allocate_w308=(select isnull(allocate,0) from u8m_wx.dbo.inv10104 where part=@part and prloc='308' )
end
else
begin
update #t_total
set onhand_w308=0,
onorder_w308=0,
allocate_w308=0
end
if exists(select * from u8m_tr.dbo.inv10104 where part=@part and prloc='002')
begin
update #t_total
set onhand_t002=(select isnull(onhand,0) from u8m_tr.dbo.inv10104 where part=@part and prloc='002' ),
onorder_t002=(select isnull(onorder,0) from u8m_tr.dbo.inv10104 where part=@part and prloc='002' ),
allocate_t002=(select isnull(allocate,0) from u8m_tr.dbo.inv10104 where part=@part and prloc='002' )
end
else
begin
update #t_total
set onhand_t002=0,
onorder_t002=0,
allocate_t002=0
end
select * from #t_total
end
