MS SQL Stored Procedure not returning value (or java needs to alter?)

Question

Can someone help me here i have a stored procedure which is not returning any value when i run the SP

Running as - so in my java code i'm doing (exec TestSP(?) with setting 1 input parameter:

on MS SQL Console running as:

exec TestSP @ErrorDescription=''

On both grounds i get nothing back.

i get no output just Command completed successfully, any idea what i need to change to make this stored procedure return a value?

--TestSP

USE [TESTDB]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Testtbl] @ErrorDescription VARCHAR(40) OUTPUT
AS
    DECLARE @status INT
    SET @status = (
                    SELECT status
                        FROM Testtbl
                  )
    IF @status = 0
        SET @ErrorDescription = 'OK'
    ELSE
        SET @ErrorDescription = 'FAIL'
    RETURN @status

is there anything i need to change at java level to make this work or SP needs to be change?

Java:

stmt = connection.prepareCall(sql);
stmt.setString(1, errorDesc);
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.executeUpdate();
String result = stmt.getString(2);

System.outprintln("Result is: "+result);

what changes need to be done at java level to make it work, i'm getting no result in variable "result"?

Is your problem with receiving the *status* value, the *error description* or both? — Damien_The_Unbeliever, Mar 24 '14 at 15:43

score 1 · Answer 1 · 2014-03-24T16:51:14.670

RETURN keyword is not used for returning a value. To return you need SELECT statement or OUTPUT variable.

Read RETURN Documenation http://technet.microsoft.com/en-us/library/ms174998.aspx

instead of RETURN @status you need SELECT @status

Below is correct example of using OUTPUT

CREATE PROC testSP
    (
        @pOut VARCHAR(40) OUTPUT
    )
AS
    BEGIN
        DECLARE @something INT = 1;

        IF @something = 0
            SET @pOut = 'Zero'

        IF @something = 1
            SET @pOut = 'One'
    END

DECLARE @outparam VARCHAR(10)
EXEC testSP @pOut = @outparam OUTPUT
SELECT @outparam

as you can see OUTPUT parameter need to be set to some value. If you don't specify OUTPUT it will be treated as input

score 0 · Answer 2 · answered Mar 24 '14 at 15:47

0

To get a return value you need to assign it to a variable.

To get an output parameter, you need to add the output keyword when calling the procedure

ie:

declare @ret int, @ErrorDescription varchar(50)
exec @ret = TestSP @ErrorDescription output
select @ret,  @ErrorDescription

answered Mar 24 '14 at 15:47

podiluska

50,950
7
98
104

when you say: you need to add the output keyword when calling the procedure - what do you mean - all i'm expecting to do is exec TestSP @ErrorDescription = '' and i expect status value back - if SP needs to be modified let me know what needs changing! – codejunkie Mar 24 '14 at 16:00
You should call it as detailed in the answer. – podiluska Mar 24 '14 at 16:22
you cant do that in java or C# like that you have to execute as i mentioned in my question. – codejunkie Mar 24 '14 at 16:30
You have to mark the parameter as an output parameter in the calling language, eg: C#. – podiluska Mar 24 '14 at 16:34
ok on out parameter, but i'm looking to get "@status" out from SP not error Description which i believe is the Out parameter. what needs to be changed on SP to make Status returned when simply executed? – codejunkie Mar 24 '14 at 16:38
It is. The problem is with how you're calling it – podiluska Mar 24 '14 at 16:41
sorry what you mean it is? are you implying SP is fine and C# needs to call it differently? – codejunkie Mar 24 '14 at 17:14
Yes, exactly. Look at the Direction property on the parameter – podiluska Mar 24 '14 at 17:20
can you give me any examples for calling SP like this? – codejunkie Mar 24 '14 at 19:04
http://stackoverflow.com/questions/3433694/how-to-run-the-stored-procedure-that-has-output-parameter-from-c – podiluska Mar 25 '14 at 08:31

MS SQL Stored Procedure not returning value (or java needs to alter?)

2 Answers2