Raise an error to ADO from a stored proc

Question

Post reply

Raise an error to ADO from a stored proc

Adam O'Neil

SSC Veteran

Points: 298
More actions
October 20, 2005 at 1:55 pm

#166476

I'm trying to raise an error from a stored procedure of mine in my VBScript/ASP code. I've been successful with simple cases, but cannot--for the life of me--get it to work with a nested proc call. In this example, I have a proc that calls another proc. I use a return value from the inner proc, test to see if it's <> 0, and raise an error. Using PRINT statements, I've verified that my error condition is occuring, but the RAISERROR statement isn't doing anything. I am pretty new to T-SQL.
--add rabies tag service
EXEC @return = AddService @animalID=@animalID, @serviceID=@serviceID
IF (@return <> 0)
BEGIN
ROLLBACK TRAN
SET @return = 5
RAISERROR ('Failed to add rabies tag service to animal.', 16, 1)
GOTO end_proc
END
When I call this proc from VB script, using arguments that should cause an error, the error isn't raised. UNLESS I cause the error is not related to the nested proc call.
The @return = 5 line is a makeshift error number for the current proc, not really meant to be part of the overall solution.
Many thanks for any help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I am a bit lost on this statement:

--add rabies tag service

EXEC @return = AddService @animalID=@animalID, @serviceID=@serviceID

Might you want to be using

--add rabies tag service

SELECT @return = AddService, @animalID=@animalID, @serviceID=@serviceID

FROM SomeTable

WHERE SomeCondition

I wasn't born stupid - I had to study.

Adam O'Neil SSC Veteran Points: 298 More actions · Answer 2

>>UNLESS I cause the error is not related to the nested proc call.<<

What am I saying here?

After much Googling, I found that I needed to use adExecuteNoRecords with my Connection.Execute method call. There were some other conditions mentioned in this article that I didn't find to be true, and I'm not sure I fully grasp what's at work here, but I'm happy I got my thing to work...

Adam O'Neil SSC Veteran Points: 298 More actions · Answer 3

>>

I am a bit lost on this statement:

--add rabies tag service

EXEC @return = AddService @animalID=@animalID, @serviceID=@serviceID

<<

Thanks for replying. The statement above is meant to branch to another stored proc and return the result. It so happens that the param names of the nested proc are named the same as the params being passed to them. Hence the redundant-looking param/value pairs. It's not meant to be a SELECT statement.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

Note for next time: Post the calling VBScript as well. If I had seen your Connection.Execute statement I could have told you it was missing the adExecuteNoRecords. It didn't even occur to me to ask since I always code adExecuteNoRecords for any Stored Procedure that isn't returning a recordset...