Using RAISERROR and @@ROWCOUNT (Transact-SQL)

[Cesare]

I have written several stored procedures that raise custom error messages
using RAISERROR. These stored procedures are being called from VB6 using ADO
sp2.

The problem that I'm experiencing is that in general the errors are detected
properly, but as soon as I try to use RAISERROR after checking any of the @@
variables (ie. @@ROWCOUNT, @@ERROR, @@FETCH_STATUS etc) the error is no
longer detected in VB.

Confusingly, if you run the sp from the query analyser the error is always
reported properly.

Stored proc code example...

**** EXAMPLE 1 ****

SELECT COUNT(Cmp_id) FROM Company WHERE Cmp_Account_No = @cmp_acc_no

IF @@ROWCOUNT <> 0
BEGIN
RAISERROR ('Account Number must be unique. Cannot Insert Company', 16, 1)
SELECT @new_cmp_id = -1
RETURN (99)
END

**** END ****

**** EXAMPLE 2 ****

DECLARE @RCount int

SELECT @RCount = COUNT(Cmp_id) FROM Company WHERE Cmp_Account_No =
@cmp_acc_no

IF @RCount <> 0
BEGIN
RAISERROR ('Account Number must be unique. Cannot Insert Company', 16, 1)
SELECT @new_cmp_id = -1
RETURN (99)
END

**** END ****

Example 1 fails to raise the error within VB and this issue must be worked
around by use of Example 2. Whilst this may seem a trivial example, (it is!)
I need to do some cursor work and check the @@FETCH_STATUS var which has
exactly the same effect, unfortunately it's not so easy to create a
workaround for.

Any help (even acknowlegement that I'm not alone) would be much
appreciated...

Cesare

[Bill Slater]

You do realize that the SQL statement in Example 1 will always return at least
one row - in which case @@ROWCOUNT will never be 0? It may be, however that
Count(Cmp_Id) will be zero :)

[chris petchey]

If this is done wth SQLServer7 then you may need the latest (just released)
SQL7 service pack. The incorrect reporting of RAISEERROR is one of the
issues corrrected

Cesare <ces...@digitalmail.nospam.com> wrote in message
news:8bvogf$2kb8$1...@quince.news.easynet.net...

[Cesare]

Unfortunately, according to Microsoft UK Help Desk, who can and have
replicated this problem perfectly, SP2 does not fix this problem. They are
currently looking into this and I'll post their response when it comes.

SP2 does fix many issues with RAISERROR most notably when using client side
cursors, unfortunately it hasn't helped me...

Cesare

chris petchey <ch...@2manykooks.freeserve.co.uk> wrote in message
news:uDjrqd0m$GA.246@cppssbbsa03...

[Tibor Karaszi]

Isn't this the ol' SET NOCOUNT ON problem?
I don't speak ADO well, but perhaps ADO is confused by the "rows affected"
(seen as "dummy" result sets in ADO) messages and that messes up the error
collection and/or error handling.

--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.

Cesare <ces...@digitalmail.nospam.com> wrote in message

news:e972vH#n$GA.298@cppssbbsa05...