例：
USE [bhis_fc_360009081027801]
GO
/****** 对象: StoredProcedure [dbo].[yp_ruku_accept] 脚本日期: 01/13/2012 23:48:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[yp_ruku_accept]
@group_no_out CHAR(6),
@draw_no INT,
@org_code VARCHAR(20)
AS
SET NOCOUNT ON
SET ROWCOUNT 0
DECLARE @charge_code CHAR(6),
@serial CHAR(2),
@retprice FLOAT,
@fix_price FLOAT,
@out_seri SMALLINT,
@buy_price FLOAT,
@issue_amount FLOAT,
@eff_date DATETIME,
@manu_no VARCHAR(12),
@license_no VARCHAR(40),
@supplyer VARCHAR(6),
@drawer CHAR(5),
@out_type CHAR(2),
@comment VARCHAR(32),
@cheque_no VARCHAR(12),
@manufacture VARCHAR(6),
@out_date DateTime,
@in_docu_no INT,
@current_stock_amount FLOAT,
@stock_amount FLOAT
declare @errorSum INT
declare @org_data VARCHAR(100) --上级库
DECLARE @_orgCode VARCHAR(20) --上级机构码
DECLARE @temSQL VARCHAR(2000) --临时执行语句
BEGIN TRANSACTION
set @errorSum=0 --初始化为,即无错误
--得到上级机构码
SET @_orgCode=SUBSTRING(@org_code,1,12)
--得到上级库名
SELECT @org_data=dbname FROM [bhis_common].dbo.public_zd_database WHERE id=@_orgCode
--得到最大入库单号
SELECT @in_docu_no=ISNULL(max(in_docu_no),0)+1 FROM yp_in_detl
--查询上级库的出库数据
set @temSQL = 'SELECT out_seri,charge_code,serial,retprice,fix_price,buy_price,issue_amount,eff_date,manu_no,license_no,supplyer,drawer,out_type,comment,cheque_no,manufacture,out_date FROM ['+@org_data+'].dbo.yp_out_detl WHERE draw_no='+CONVERT(nVARCHAR(100), @draw_no)+' and wss_org_no='''+@org_code+''''
--定义游标(动态)
EXEC('DECLARE myCur CURSOR FOR '+@temSQL)
OPEN myCur
FETCH NEXT FROM myCur
INTO @out_seri,@charge_code,@serial,@retprice,@fix_price,@buy_price,@issue_amount,@eff_date,@manu_no,@license_no,@supplyer,@drawer,@out_type,@comment,@cheque_no,@manufacture,@out_date
WHILE(@@FETCH_STATUS<>-1)
BEGIN
--得到现有库存
SELECT @stock_amount= stock_amount FROM yp_base WHERE charge_code=@charge_code and serial=@serial AND group_no=@group_no_out AND org_code=@org_code
--得到当前总库存
set @current_stock_amount=ISNULL(@stock_amount,0)+ISNULL(@issue_amount,0)
--入库
INSERT INTO yp_in_detl
(in_date,in_seri,charge_code,serial,retprice,fix_price,buy_price,buy_amount,current_stock_amount,stock_amount,eff_date,manu_no,rept_no,
in_docu_no,license_no,supplyer,purchaser,checker,in_type,report_date,print_flag,check_flag,check_oper,acct_date,comment,group_no,cheque_no,
acct_oper,acct_pageno,manufacture,group_out,invite_bidding_flag,back_in_date,back_in_seri,out_date,out_no,org_code)
VALUES
(GETDATE(),@out_seri,@charge_code,@serial,@retprice,@fix_price,@buy_price,@issue_amount,isnull(@current_stock_amount,0),isnull(@stock_amount,0),@eff_date,@manu_no,
NULL,@in_docu_no,@license_no,@supplyer,@drawer,'','11',NULL,'0','0',NULL,NULL,@comment,@group_no_out,@cheque_no,NULL,NULL,@manufacture,
@group_no_out,'0',NULL,NULL,@out_date,@draw_no,@org_code)
set @errorSum=@errorSum+@@error --累计是否有错误
--更新库存
update yp_base set stock_amount=@current_stock_amount,stock_amount2=@current_stock_amount,open_flag='1' where charge_code=@charge_code and serial=@serial and org_code=@org_code and group_no=@group_no_out
set @errorSum=@errorSum+@@error --累计是否有错误
--更新上级出库单状态
set @temSQL = 'UPDATE ['+@org_data+'].dbo.yp_out_detl SET accept_flag = 1, accept_date=getdate() WHERE draw_no='+CONVERT(nVARCHAR(100), @draw_no)+' AND out_seri='+CONVERT(nVARCHAR(100), @out_seri)+' and wss_org_no='''+@org_code+'''';
EXEC(@temSQL);
set @errorSum=@errorSum+@@error --累计是否有错误
--
set @temSQL='UPDATE ['+@org_data+'].dbo.yp_out_detl_input SET accept_date=getdate() WHERE draw_no='+ CONVERT(nVARCHAR(100), @draw_no)+' AND out_seri='+CONVERT(nVARCHAR(100), @out_seri)+' and wss_org_no='''+@org_code+'''';
EXEC(@temSQL);
set @errorSum=@errorSum+@@error --累计是否有错误
FETCH NEXT FROM myCur
INTO @out_seri,@charge_code,@serial,@retprice,@fix_price,@buy_price,@issue_amount,@eff_date,@manu_no,@license_no,@supplyer,@drawer,@out_type,@comment,@cheque_no,@manufacture,@out_date
END
CLOSE myCur
DEALLOCATE myCur
if @errorSum<>0
begin
rollback transaction --回滚(撤销)事务
raiserror ('ERROR',11,1)
end
else
begin
commit transaction --提交事务
raiserror ('OK',11,1)
end
