June 15, 2007 at 5:54 am
Hi all,
Is it possible to turn off an error message that is generated by a query in a stored proc?
(You can turn off warnings, but I have not found any way to turn off errors...)
Best regards,
Nicklas
June 15, 2007 at 10:19 am
You can't turn off errors in SQL 2000 and the try catch logic isn't available till 2005 (http://technet.microsoft.com/en-us/library/ms175976(SQL.90).aspx)
In SQL 2000 If an error is encountered the client will recieve the error message. When running the proc in Query Analyzer you will always see the error message. You have error handling control if your SP is executed via an Application using ADO or DMO etc, but in QA or Jobs etc, the only way to avoid the error message being printed is check for the error before hand using IF EXISTS, or IF NOT EXISTS or IF(Select Column... ) = X etc
Using this method you'll know in Proc before hand if an error is going to occur and you can choose to either skip the code that would have produced the error, raise your own error, execute the code anyway, it's really up to you, which is what you are going for, control over what happens.
Here is an example
IF NOT EXISTS (Select 1 from dbo.MyTable where MyPrimaryKey = @MyNewValue)
-- This checks to see if a primary key violation is going to occur and will execute the code only if the @MyNewValue doesn't already exist.
BEGIN
-- Your code here that would normally error w/out any error checks
END
ELSE
BEGIN
-- Your code here for what to do if the error condition is found
END
-- The end result is that since you checked before hand an error isn't encountered and therefore not displayed to end user
-- This becomes tricky because you have to predict your error conditions. Any error condition not checked for results an
-- error messge to the client.
-
June 18, 2007 at 12:21 am
Thank you Jason for your reply!
It helped a lot…
BR,
Nicklas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply