Get Out Parameters on Stored Procedures (MSSQL Server)

445 views
Skip to first unread message

Marco Vargas

unread,
Dec 16, 2013, 3:40:08 PM12/16/13
to sq...@googlegroups.com
Hi,

 If I have a stored procedure as the following (MSSQL Server):

CREATE PROCEDURE [dbo].[play_GetData]
    @returnedStatus  INT           OUTPUT,
    @returnedMessage VARCHAR(2000) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        --******************************************* Assume the query is quite more complicated.
        SELECT Column1, Column2, Column3
        FROM  TableName
        --*******************************************
        SET @returnedStatus = 0
        SET @returnedMessage = 'SUCCESS' 
    END TRY
    --
    BEGIN CATCH
        --
        SET @returnedStatus = ERROR_NUMBER()
        SET @returnedMessage = ERROR_MESSAGE()    
        RAISERROR(@returnedMessage, 16, 1)
        --
    END CATCH
    --
END

How could I execute the stored procedure, get the data and check the content of the output parameters using sql2o???

Thanks

marco.

Lars Aaberg

unread,
Dec 16, 2013, 5:21:40 PM12/16/13
to sq...@googlegroups.com
Hi,

I got the following example to work.

On the Sql Server i created this stored procedure:

CREATE PROCEDURE [dbo].[output_param_test]
    @inputParam varchar(100),
    @outputParam varchar(100) OUTPUT

AS
BEGIN
    SET NOCOUNT ON;

    set @outputParam = @inputParam;
END

To get the output parameter from java with sql2o:
        String sql =
                "declare @outputParam as varchar(100) " +
                "exec output_param_test @inputParam = :param, @outputParam = @outputParam OUTPUT " +
                "select @outputParam as myval";

        String outputVal = sql2o.createQuery(sql).addParameter("param", "foo").executeScalar(String.class);
        assertThat(outputVal, is(equalTo("foo")));

If you need to fetch more than one output parameter, you can use executeAndFetchTable() method instead of the executeScalar() method.

Regards
Lars Aaberg

Marco Vargas

unread,
Dec 17, 2013, 9:18:47 AM12/17/13
to sq...@googlegroups.com
But in this case we just can get one cursor from the database side, I mean, if the stored procedure performs a select statement we won't be able to get the output parameters, since the executeAndFetch will just bring the select statement specified inside the stored procedure.

Or is there any way to get more than one cursor from the database?

By the way, thanks Lars for your quick responses.

Lars Aaberg

unread,
Dec 18, 2013, 2:16:19 AM12/18/13
to sq...@googlegroups.com
Hi Marco,

The preferred way of getting output parameters with JDBC is using the CallableStatement, which has a registerOutParameter() method. Unfortunately there is no wrapper around this functionality in sql2o. So I don't think it's possible to get both the resultset and the output parameters with Sql2o at this time.

It would be great if you made an issue on github about this, then I will see if I can fix this in the near future.

Regards
Lars Aaberg


--
You received this message because you are subscribed to the Google Groups "sql2o" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sql2o+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Marco Vargas

unread,
Dec 18, 2013, 10:41:54 AM12/18/13
to sq...@googlegroups.com
Thanks Lars for your quick replies!

I just added the issue in Github.

Please keep me posted.

By the way!

Merry Christmas!

VAMSI KRISHNA MOGALIPUVVU

unread,
May 18, 2019, 3:17:26 AM5/18/19
to sql2o

Hi Lars Aaberg,

We would like to implement database interaction with sql2o and java restful api  for an angular project . we are using some stored procedures which returns both resultset and output parameters. with sql2o.  Is this possibe ?
Did you fix this in latest version of sql2o ?

Thanks in advance
To unsubscribe from this group and stop receiving emails from it, send an email to sq...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages