3

I’m calling MsSql procedures from JAVA to create invoices using following code:

CallableStatement cstmt = conn.prepareCall(sql);
cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
cstmt.execute();
resultValue = cstmt.getString(1);
…

And in my procedure I use select @cErrorOut; for debugging purposes.

But the catch is here if I use select like in example above I don’t get SqlException from RAISERROR (@cErrorOut,16,1); But if I remove select everything looks ok. And if i call procedure from ManagementStudio i get an error as expected with or without select. Why is that?

Here are working and not working example:

Example where we don’t get sql exception because of select (NOT OK):

-- create item  
EXEC dbo.pHE_MoveItemsCreAll @ahKey, @cIdent,@nQty, @cNote, @nhUserId, @nNo OUTPUT,@cErrorOut OUTPUT,@cStatus OUTPUT;  

select @cErrorOut;

-- check for error status
if (@cStatus <> 'T') begin
    EXEC dbo._CreateInvoiceRollback @ahKey;
    RAISERROR (@cErrorOut,16,1);
end;

Example where we get sql exception (OK):

-- create item  
EXEC dbo.pHE_MoveItemsCreAll @ahKey, @cIdent,@nQty, @cNote, @nhUserId, @nNo OUTPUT,@cErrorOut OUTPUT,@cStatus OUTPUT;  

-- check for error status
if (@cStatus <> 'T') begin
    EXEC dbo._CreateInvoiceRollback @ahKey;
    RAISERROR (@cErrorOut,16,1);
end;

Selecting in variables works fine – exceptions are not affected:

select @anVat = anVAT from tHE_SetTax where acVATCode = @defaultVatCode;

Im using MsSql server 2008 r2.

Kiki
  • 2,243
  • 5
  • 30
  • 43
  • Can you show the full code of the prepared statement? – fge Apr 24 '15 at 07:19
  • @fge you mean the value of sql variable? If yes there is nothing special in there just normal String variable with exec procedure call. And the value is apended depending on invoice item parameters. So we have something like: exec _CreateInvoiceItems v1, v2,.... – Kiki Apr 24 '15 at 07:25

0 Answers0