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
– Damien_The_Unbeliever, Commented Mar 24, 2014 at 15:43

score 1 · Accepted Answer · 2014-03-24 16:51:14Z

1

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

edited Mar 24, 2014 at 16:51

answered Mar 24, 2014 at 15:31

user275683

Sign up to request clarification or add additional context in comments.

Comments

podiluska · Accepted Answer · 2014-03-24 15:47:30Z

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, 2014 at 15:47

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

10 Comments

codejunkie Over a year ago

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!

podiluska Over a year ago

You should call it as detailed in the answer.

codejunkie Over a year ago

you cant do that in java or C# like that you have to execute as i mentioned in my question.

podiluska Over a year ago

You have to mark the parameter as an output parameter in the calling language, eg: C#.

codejunkie Over a year ago

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?

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related