How to return success/failure result from a Stored Procedure ? 2

[royc75]

Hello,

I am running a Stored Procedure which doesn't generate any ResultSet. I would like to know from my code if the SP was completed succefully. Is it possible to return such indication at the end of the SP?

 

[SQLDenis]

exec @i =ProcedureName
@i will not be 0 if there is an error

here is some sample script

create procedure prTest
as
select getdate()
go

create procedure prTest2
as
select getdate()
return @@error
go



declare @i int
exec @i =prTest
select @i
go

declare @i int
exec @i =prTest2
select @i

drop procedure prTest,prTest2
go

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

Of course. You could return 1 for success and 0 for failure.

....... -- SP main code
IF sucess
   RETURN 1
ELSE  -- Not needed but make code more readable
    RETURN 0

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[royc75]

Hi,

Thank you both.
The question is if I will able to read this return value from Java code or it's just for other SPs? After all this return value is not a ResultSet and As far as I know from code I can only read ResultSet's results...

 

[SQLDenis]

example from here (

http://forum.java.sun.com/thread.jspa?threadID=695980&messageID=4041872)

CREATE proc testproc AS
BEGIN
-- My return code
return 7
END
go



Then a sample patchy buggy code to play around:

import java.sql.*;
 
public class ProcTesting {
    public static void main(String[] args) {
        String connUrl          = "jdbc:sybase:Tds:myserver:5150/dbinst";
        String userName         = "username";
        String password         = "password";
 
        Connection con          = null;
        CallableStatement stmt  = null;
        ResultSet rs            = null;
 
        String sql = "{? = call testproc}";
        try {
            Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
            con     = DriverManager.getConnection(connUrl, userName, password);
            stmt    = con.prepareCall(sql);
            stmt.registerOutParameter(1, Types.INTEGER);
            stmt.execute();
            System.out.println(stmt.getInt(1));
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(con, stmt);
        }
    }
 
    public static void closeAll(Connection con, Statement stmt) {
        try {
            con.close();
        } catch(Exception e) {  }
 
        try {
            stmt.close();
        } catch(Exception e) {  }
    }

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

I am not sure about Java, But in VFP I can read it.
I hope somebody with more knowledge in JAVA can answer you.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[royc75]

Thank you both again, I beleive the sample code that Denis provided will do.