CODEWITHSUNDEEP
javasqlstored-proceduresjdbcmssql-jdbc
codejunkie
codejunkie

Reputation: 1122

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

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"?

Upvotes: 1

Views: 4060

Answers (2)

user275683
user275683

Reputation:

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

Upvotes: 1

podiluska
podiluska

Reputation: 51494

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

Upvotes: 0

Related Questions