on 03-13-2008 6:43 PM
I need to return an output parameter from a stored procedure that is doing an insert. Ultimately this needs to be done in an xMII Logic Editor Transaction. Can this be done with a SQL Query in Command mode? If, so how?
If your SQL query returns a dataset then you can used FixedQuery mode. Command mode does not expect a resultset (just the db acknowledgement that it either succeeded or failed).
Regards,
Jeremy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The stored proc I am calling performs an update and returns an error code in an output parameter. It does not return a dataset. The system I am interfacing to uses stored procs w/output params for all functions. I believe I need to use iCommands because of the update. Here is the procedure and parameters.
CREATE PROCEDURE [dbo].[stub_auto_pman]
(
@i_src_loc_num char(10),
@i_lpn char(20),
@i_item_num char(30),
@i_qty numeric(10),
@o_ret_status numeric OUTPUT
)
Thanks
I have created the iCommand and successfully executed it, however I want to retreive data from the output parameter and don't know how to do that.
I can call the SP with an iCommand as:
EXEC dbo.xMII_stub_auto_pman @i_src_loc_num='Param.1', @i_lpn='Param.2', @i_item_num='Param.3', @i_qty=Param.4, @o_ret_status=Param.5
But I cannot retreive the value from the @o_ret_status parameter.
I tried to capture the output parameter to a variable and return it:
DECLARE @ret_status as numeric
EXEC dbo.xMII_stub_auto_pman @i_src_loc_num='Param.1', @i_lpn='Param.2', @i_item_num='Param.3', @i_qty=Param.4, @o_ret_status=@ret_status
SELECT @ret_status
But I get the following error:
Fatal Error
A SQL Error has occurred on query, TDS DriverA ResultSet was produced by 'DECLARE @ret_status as numeric EXEC dbo.xMII_stub_auto_pman @i_src_loc_num='a', @i_lpn='b', @i_item_num='c', @i_qty=4, @o_ret_status=@ret_status SELECT @ret_status'.
Any ideas?
Edited by: Michael Janssen on Mar 14, 2008 7:14 PM : Items in brackets were converted to links, removed brackets
Hari,
Thanks for the idea, but can I do it completely in a xMII Business Logic transaction. I won't have a web page to use the javascript and DOM to get at the value. I just want a scheduled transaction reading one system calling a stored proc in another.
Thanks, I think I am getting close to the answer.
Your FatalError is telling you that a resultset is being produced, so you need to use FixedQuery in your SQLQuery template. This way you will get the result from your procedure.
In an SQLQuery template:
FixedQuery Mode expects results.
Command Mode does not expect results.
In both modes, the query request string is passed to the JDBC driver, but it all depends upon how it reacts to the results/response.
Regards,
Jeremy
Thanks Jeremy,
I was able to execute the following:
DECLARE @ret_status as numeric
EXEC dbo.xMII_stub_auto_pman @i_src_loc_num='Param.1', @i_lpn='Param.2', @i_item_num='Param.3', @i_qty=Param.4, @o_ret_status=@ret_status OUTPUT
SELECT @ret_status
in a FixedQuery and it INSERTED the data and returned the output parameter (as a dataset).
Edited by: Michael Janssen on Mar 14, 2008 7:16 PM : Items in brackets were converted to links, removed brackets
User | Count |
---|---|
10 | |
5 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.