1、SQL SERVER存儲過程如下：
CREATE PROCEDURE [dbo].[Pro_ApplyFrom_FetchERP] (
@typeNo varchar(12),
@updType char(1),
@xmlData nvarchar(4000),
@userName nvarchar(20) = '工廠轉入',
@applyNo varchar(50) output,
@reContent nvarchar(1000) output
)
AS
if @@trancount > 0
save tran Pro_ApplyFrom_FetchERP
begin tran Pro_ApplyFrom_FetchERP
if @typeNo = 'A'
begin
exec Pro_ApplyFrom_FetchERP_finish @updType,@xmlData,@userName,@applyNo output,@reContent output
end
if @@error > 0
goto Error_Handle
set nocount off
commit tran
return 0
Error_Handle:
set nocount off
rollback tran Pro_ApplyFrom_FetchERP
return 1
2、PB調用代碼如下：
declare Pro_ApplyFrom_FetchERP procedure for
@RC = dbo.Pro_ApplyFrom_FetchERP
@typeNo = 'A',
@updType = :as_updtype,
@xmlData = :ls_xml,
@userName = :ls_upd_user,
@applyNo = :ls_web_af_no output,
@reContent = :ls_rtn output
using ata_sqlserver;
execute Pro_ApplyFrom_FetchERP;
if ata_sqlserver.sqlcode = 0 then
fetch Pro_ApplyFrom_FetchERP into :li_rtn,:ls_web_af_no,:ls_rtn;
if ata_sqlserver.sqlcode < 0 then
messagebox("sql server fetch error",ata_sqlserver.sqlerrtext)
return -1
end if
else
messagebox('將送貨單資料轉至物品攜出單系統時出錯:' + string(ata_sqlserver.sqlcode),ata_sqlserver.sqlerrtext)
return -1
end if
close Pro_ApplyFrom_FetchERP;
3、問題：當PB代碼執行到FETCH INTO出錯提示：Mismatch between retreive columns and fetch columns
------解决方案--------------------
以下为某例,请参考:
- SQL code
声明存储过程
CREATE PROCEDURE sp_results_demo @InParmInt Int,@OutParmInt OUTPUT
AS
BEGIN
SELECT 1,2,3,4
SELECT uid,id
FROM sysobjects
WHERE type='U'
SELECT @OutParmInt=69
Return 16
END
用于返回每个返回值时用PowerScrip如下：
Long lReturn,lPram1,lPram2,lResult1,lResult2,lResult3,lResult4,lResult5,lResult6
DECLARE results_demo PROCEDURE FOR
[email protected]=sp_results_demo
[email protected] = :lPram1,
[email protected] = :lPram2 OUTPUT using sqlca;
execute results_demo;
Do While SQLCA.SQLCode=0
Fetch results_demo INTO :lResult1,:lResult2,:lResult3,:lResult4 ;
Loop
Fetch results_demo INTO :lResult5,:lResult6 ;
Do While sqlca.sqlcode=0
Fetch results_demo Into :lResult5,:lResult6;
Loop
Fetch results_demo Into :lReturn,:lPram2;
Close results_demo ;