August 10, 2006 at 11:40 am
alter Procedure testLoad
(
@FileName varchar(50),
@Logon varchar(20),
@BusArea_out char(2)OUTPUT,
@PayId_out char(3)OUTPUT,
@CycleNo_out char(7)OUTPUT
)
As
SET NOCOUNT ON
DECLARE @File_Exists int,
@textfile char(150),
@BusArea char(2),
@PayId char(3),
@Login varchar(20),
@CycleNo char(15),
@FHBusArea char(2),
@FHPayId char(3),
@FHCycleNo char(15),
@TrBatchPoNo char(10),
@rename varchar(255),
@file_extn datetime
SET @BusArea=(select substring(Col001,194,2)FROM SIRSInput WHERE substring(Col001,1,6) = 'HEADER')
SET @PayId=(select substring(Col001,196,3)FROM SIRSInput WHERE substring(Col001,1,6) = 'HEADER')
SET @CycleNo=(select substring(Col001,199,7)FROM SIRSInput WHERE substring(Col001,1,6) = 'HEADER')
SELECT @BusArea,@BusArea_out out
SELECT @PayId,@PayId_out out
SELECT @CycleNo,@CycleNo_out out
END
GO
In the above SP, how can I pass BusArea,PayId,CycleNo to the VB application.
August 10, 2006 at 12:21 pm
Use single select SELECT @BusArea, @PayId,@CycleNo instead of three select statements
August 10, 2006 at 12:31 pm
There is no difference.
When I exec my SP it expects those output parameters, y?
August 11, 2006 at 5:53 am
Any Help
August 11, 2006 at 6:37 am
alter Procedure testLoad
(
@FileName varchar(50),
@Logon varchar(20),
@BusArea_out char(2)OUTPUT,
@PayId_out char(3)OUTPUT,
@CycleNo_out char(7)OUTPUT
)
As
SET NOCOUNT ON
DECLARE @File_Exists int,
@textfile char(150),
@BusArea char(2),
@PayId char(3),
@Login varchar(20),
@CycleNo char(15),
@FHBusArea char(2),
@FHPayId char(3),
@FHCycleNo char(15),
@TrBatchPoNo char(10),
@rename varchar(255),
@file_extn datetime
SELECT @BusArea = SUBSTRING(Col001,194,2),
@PayId = SUBSTRING(Col001,196,3),
@CycleNo = SUBSTRING(Col001,199,7)
FROM SIRSInput
WHERE Col001 LIKE 'HEADER%'
END
GO
In VB6
Dim ADOCommand As New ADODB.Command
ADOCommand.ActiveConnection = Connection 'name of connection
ADOCommand.CommandType = adCmdStoredProc
ADOCommand.CommandText = "testLoad"
ADOCommand.Parameters("@FileName") = Filename 'Set input parameters
ADOCommand.Parameters("@Logon") = Logon
ADOCommand.Execute
Then you can retrieve the output
ADOCommand.Parameters("@BusArea_out")
ADOCommand.Parameters("@PayId_out")
ADOCommand.Parameters("@CycleNo_out")
Why do you not pass the output as a recordset and pass the whole of Col001 and get the VB app to split it?
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply