How do i get the output of an stored procedure??

[RicardoPereira]

Hi,

I have a stored procedure that print an output whenever the statment has errors. Something like:

create procedure teste as
select * from nonexistentTabe
if (@@error <> 0)
Print 'Error on statement'

How do i get the Error on statement message?

thanks
Ricardo pereira

 

[venur]

Hi,

I am not sure this works, just a try. What happens if we return the error back to the caller.

Ex :
create procedure teste as
DECLARE @myERROR int

SET NOCOUNT ON

select * from nonexistentTabe

SELECT @myERROR = @@ERROR

IF @myERROR != 0 GOTO HANDLE_ERROR

RETURN 0

HANDLE_ERROR:
ROLLBACK TRAN
RETURN @myERROR


In Java Code call the prodecure

// Call a procedure with one OUT parameter
cs = connection.prepareCall(&quot;{call teste(?)}&quot;
// Register the type of the OUT parameter
cs.registerOutParameter(1, Types.INT);
// Execute the stored procedure and retrieve the OUT value
cs.execute();
String errorNo = cs.getInt(1); // OUT parameter

It should return the Error Code. Use that Error code to print the Error on the SQL Anaylizer.

PRINT CONVERT(varchar(10), errorNo)

I dont think you can get the message stack back as it is thrown by the SQL Server on its side.

Its just a Try.. you might get better idea's just look out.

Cheers,
Venu