How to get OUTPUT parameter of stored procedure sp_setapprole in SQLServer

Question

I'm using the following JDBC driver (the one from Microsoft): http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx.

I want to retrieve the OUTPUT parameter (@cookie) of the stored procedure sp_setapprole. I can call the procedure fine like this, but I can't figure out how to retrieve the OUTPUT parameter.

statement = connection.createStatement();
statement.execute("EXEC sp_setapprole '" + applicationRoleName + "', '" + applicationRolePassword + "'");

I tried with a PreparedStatement and a CallableStatement and I always get the following exception: Stored procedure 'sys.sp_setapprole' can only be executed at the ad hoc level.. I found this post: https://stackoverflow.com/a/6944693/1362049, but I don't like the solution (use another JDBC driver).

So my question: how to get OUTPUT parameter from stored procedure sp_setapprole in SQLServer using a Statement.

Answer 1

I think this will help. I am not a big java program but lived with ODBC for years.

http://www.tutorialspoint.com/jdbc/jdbc-statements.htm

Look at the callable statement. You need to define the in/out, or inout parameters. Execute the SP and read the output.

In C#, it is just looking at the parameter such as below give you the value. But the idea of making a connection, binding parameters, making the call and reading the output are the same.

my_Cmd.Parameters["@PageCount"].Value.ToString();

Here is a article from MSDN in C++.

http://technet.microsoft.com/en-us/library/ms403283.aspx

Same idea, bind parameters, make the call, read the output using [SQLExecDirect]

Give me a holler if you do not get it.