Possible bug with TMSStoredProc and RAISEERROR / Exceptionsq

[sLesae]

Hi AndreyZ,

I do understand that SDAC expects one record to get deleted. So ... if I understand it correctly if something gets deleted by my stored procedure it should return or say 1 row(s) affected. But since I've been using a Stored Procedure in my SQLDelete ( EXEC P_DELETE_TEST @TestId = :Old_TestId, @Message = '' ) it will probably expect that stored procedure to return that same message ?

If my thinking is correct, would that also mean that using a Stored Procedure in the SQLUpdate / SQLInsert would react in the same way ? Would those also expect a message like '(1 row(s) affected)' ?

If that is the case, then any Stored Procedure used instead of a Dataset.Insert, Update or Delete shouldn't contain a SET NOCOUNT ON right ?

Regards,


Stefaan

[sLesae]

Hi Andrejz,

The reason why I am asking these questions is because we also have situations where we use a Stored Procedure to delete a Master Record with all it's detail records (Orders and OrderLines). Both of those are Stored Procedures ... eg ... a stored procedure P_DELETE_ORDERLINE and P_DELETE_ORDER. For one we pass in the OrderLineId and it deletes the OrderLine, for the other we pass in the OrderId and it deletes the order.

From delphi in the SQLDelete of the Order dataset we call the P_DELETE_ORDER and in the SQLDelete of the OrderLine we call P_DELETE_ORDERLIINE. But in P_DELETE_ORDER we also have a cursor which loops over all order lines and for each order line it finds it will also call P_DELETE_ORDERLINE. The P_DELETE_ORDER will also delete another record in another table.

So ... P_DELETE_ORDER contains the following statement :

Cursor
P_DELETE_ORDER_DETAIL
Cursor end
DELETE FROM T_TASK WHERE OrderId = @OrderId
DELETE FROM T_ORDER WHERE OrderId = @OrderId

What I noticed earlier is that if there are no records in T_TASK the stored procedure will return :

(0 row(s) affected) - The DELETE FROM T_TASK
(1 row(s) affected) - The DELETE FROM T_ORDER

And this will still result in the 'Update failed. Found 0 records' message. It seems to me as if it is using the first message (0 row(s) affected) while there is a (1 row(s) affected) message too.

In the case where there are 2 records in T_TASK the stored procedure will return :

(2 row(s) affected)
(1 row(s) affected)

Which in Delphi ends up generating an 'Update failed. Found 2 records'.

Is there any way I can solve this so that it uses the last message about affected rows ?

[AndreyZ]

To avoid the problem, you should use the following code in your stored procedure:

Code: Select all

SET NOCOUNT ON;
DELETE FROM T_TASK WHERE OrderId = @OrderId;
SET NOCOUNT OFF;
DELETE FROM T_ORDER WHERE OrderId = @OrderId;

In this case, the number of affected records that is returned by the first DELETE statement is ignored. Only the number of affected records that is returned by the second DELETE statement is taken into account.

[sLesae]

Hi again,

I have indeed solved it this way. Hope everything will be fine now. Thanks for your feedback on this matter.

Regards,


Stefaan

[AndreyZ]

I am glad I could help.