Getting Output from stored procedure

[Former Member]

Hi

I am using SQL server 2005 database. I have written 1 Store procedure with 2 output paramenter.

How to get these parameter in MII's BLS.

I have used Fixed query, Fixed Query with output and command modes in Query action block.

but these are not working for me???

[Former Member · Accepted solution]

A couple of things..

First, if your outputing the BLS from a stored procedure, don't bother using output parameters, you are much better off just using a select statement. Something like

Select @Param1 as 'Param1', @Param2 as 'Param2'

That way BLS will just treat the output like it would any other type of query.

Second. If your stored procedure is doing inserts or updates, be sure to add SET NOCOUNT ON a right under your AS statement. This will prevent SQL server from sending out row update information that JDBC driver can't handle.

[Former Member]

One thing I have noticed is that if you don't alias the return value MII has trouble with it because there is no column name in the result xml.

[Former Member]

@Emmett :

In fix Query mode It throws an error: The Statement did not return a result set.

In Command Query Mode it returns Command Query Successful.

@Jamie

thanks Jamie,

I have tried the solution but none of them are working.

and this question is also unanswered.

@Christian

I tried to Alias the values.

but problem is same.

[sidnooradarsh]

Hi Anshul,

Could you please briefly explain what is your SP doing and how are you returing the output?

If you refer to the last post in the thread suggested by Jamie then there you can see how to capture the returned values from SP if you are returning just the individual variables as output

But if you are returning a table output then no need for anything to capture the output just use Fixed Query and put in this syntax.

EXEC StoredProcedureName 'Inputparam if any'

You have also mentioned that,

@Emmett : 
In fix Query mode It throws an error: The Statement did not return a result set.
In Command Query Mode it returns Command Query Successful.

I am guessing that in SP you are trying to insert something and return the last inserted value or results corresponding to inserted value, if this is the case please test the SP in Query Analyser whether its returning the results for you.

Make sure that the you initialize the output variables with some default values because say for example that based on some condition you are trying to assign values to the output variables what happens if that condition fails and there is no default values assigned to output variables.

Hope this helps!!

Regards,

Adarsh

[jamie_cawley]

Searching should yield you some results, such as

Jamie

[Former Member]

I am presuming the stored procedure provides a result set when you run it from the SQL Server ?

And i am also assuming you are running and execute command from the fixed query pane - and passing the parameters to the Stored procedure via the Query parameters pane

Are you seeing anything returned to you - error or otherwise ?

Regards,

Emmett