Error Handling in procs etc.

In the last hour a few people have referred to the site: http://www.sommarskog.se/
error-handling-II.html
I am trying to figure out the error checking. I used the document and the bo
l. For example, I cut this from the bol:
USE pubs
GO
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
IF @.@.ERROR = 547
print 'A check constraint violation occurred'
Once I FIXED the code (the bol uses double quotes) I was able to see the pri
nt statement appear. That was an improvement.
Now in the sample code from http://www.sommarskog.se/error-handling-II.html
he returns a value. When I modify my code like this:
USE pubs
GO
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
IF @.@.ERROR = 547
return 12
(12 is just a made up number to test with)
I get:
Server: Msg 178, Level 15, State 1, Line 7
A RETURN statement with a return value cannot be used in this context.
How CAN I return something?
Thanks.Roger,
Try putting your code inside a stored procedure.
create procedure dbo.p1
@.old_aid varchar(11),
@.new_aid varchar(11)
as
set nocount on
UPDATE dbo.authors
SET au_id = @.new_aid
WHERE au_id = @.old_auid
return @.@.error
go
declare @.rv int
declare @.error int
exec @.rv = dbo.p1 '172-32-1176', '172 32 1176'
set @.error = isnull(nullif(@.rv, 0), @.@.error)
print @.error
go
AMB
"Roger Twomey" wrote:

> In the last hour a few people have referred to the site: http://www.sommarskog.s
e/error-handling-II.html
> I am trying to figure out the error checking. I used the document and the
bol. For example, I cut this from the bol:
> USE pubs
> GO
> UPDATE authors SET au_id = '172 32 1176'
> WHERE au_id = '172-32-1176'
> IF @.@.ERROR = 547
> print 'A check constraint violation occurred'
>
> Once I FIXED the code (the bol uses double quotes) I was able to see the p
rint statement appear. That was an improvement.
> Now in the sample code from [url]http://www.sommarskog.se/error-handling-II.html[/url
] he returns a value. When I modify my code like this:
> USE pubs
> GO
> UPDATE authors SET au_id = '172 32 1176'
> WHERE au_id = '172-32-1176'
> IF @.@.ERROR = 547
> return 12
> (12 is just a made up number to test with)
> I get:
> Server: Msg 178, Level 15, State 1, Line 7
> A RETURN statement with a return value cannot be used in this context.
> How CAN I return something?
> Thanks|||>>
Server: Msg 178, Level 15, State 1, Line 7
A RETURN statement with a return value cannot be used in this context.
How CAN I return something?
By doing so from within a stored procedure. if you are not in a stored
procedure, where are you returning to? PRINT instead, or SELECT, or issue
your own RAISERROR().
A|||Here is my code:
CREATE PROCEDURE dbo.prTest
AS
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
IF @.@.ERROR = 547
Begin
return 123
End
select 'fred'
go
When I run the proc I get this:
Server: Msg 547, Level 16, State 1, Procedure prTest, Line 4
UPDATE statement conflicted with COLUMN CHECK constraint 'CK__authors__au_id
__0CBAE877'. The conflict occurred in database 'Pubs', table 'authors', colu
mn 'au_id'.
The statement has been terminated.
This is exactly the same result I get if there is no @.@.Error code. I don't s
ee how this error checking code did anything?
Shouldn't I see something different?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:u0ZJrGvYGH
A.4760@.TK2MSFTNGP03.phx.gbl...
> Server: Msg 178, Level 15, State 1, Line 7
> A RETURN statement with a return value cannot be used in this context.
>
> How CAN I return something?
>
> By doing so from within a stored procedure. if you are not in a stored
> procedure, where are you returning to? PRINT instead, or SELECT, or issue
> your own RAISERROR().
>
> A
>
>|||> Shouldn't I see something different?
Why do you say that? Think about it:
The update is executed. It encounters an error on a row. The error is return
ed to the client
application.
The IF statement is executed and the condition is true
The RETURN statement is executed, returning the value 123 to the calling pro
cedure/application.
You don't see the return value because no-one is catching and doing anything
with that value.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message news:OctDzvvYGHA.4060@.
TK2MSFTNGP02.phx.gbl...
Here is my code:
CREATE PROCEDURE dbo.prTest
AS
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = '172-32-1176'
IF @.@.ERROR = 547
Begin
return 123
End
select 'fred'
go
When I run the proc I get this:
Server: Msg 547, Level 16, State 1, Procedure prTest, Line 4
UPDATE statement conflicted with COLUMN CHECK constraint 'CK__authors__au_id
__0CBAE877'. The
conflict occurred in database 'Pubs', table 'authors', column 'au_id'.
The statement has been terminated.
This is exactly the same result I get if there is no @.@.Error code. I don't s
ee how this error
checking code did anything?
Shouldn't I see something different?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u0ZJrGvYGHA.4760@.TK2MSFTNGP03.phx.gbl...
> Server: Msg 178, Level 15, State 1, Line 7
> A RETURN statement with a return value cannot be used in this context.
> How CAN I return something?
> By doing so from within a stored procedure. if you are not in a stored
> procedure, where are you returning to? PRINT instead, or SELECT, or issue
> your own RAISERROR().
> A
>|||When executing the stored procedure you must declare (and use) a variable to
assign the return value from the procedure to:
declare @.r int
exec @.r = <procedure> <parameter list>
print @.r
What exactly are you trying to achieve?
ML
http://milambda.blogspot.com/|||Well... At this point I am trying to achieve the Zen state of knowlege. ;)
I am trying to figure out how this works so that I might be able to apply it
to something in the future.
Currently a huge amount of my .net code is error checking. Because I have
never been able to figure out the @.@.Error in SQL server I rely on my .Net
code to figure out what happened and fix it.
I think it would be ever so much easier if I understood @.@.Error use so that
I could take care of some of this in SQL itself. The sample I gave you is
just me trying to figure out what the heck is happening.
Okay. Executing the way you suggested worked. Now I just have to figure out
how I can use that knowlege.
For example, could my variable use to hold the error state be an output
param? I know how to get those from vb.net.
It's amazing the things I don't know! ;)
Thanks for your help.
Anyway, I will try
"ML" <ML@.discussions.microsoft.com> wrote in message
news:66BD512F-C1AE-4CD8-8C40-575153F30B9C@.microsoft.com...
> When executing the stored procedure you must declare (and use) a variable
> to
> assign the return value from the procedure to:
> declare @.r int
> exec @.r = <procedure> <parameter list>
> print @.r
> What exactly are you trying to achieve?
>
> ML
> --
> http://milambda.blogspot.com/|||As suggested before, read through Erland's articles. That will give you the
basics and a few good practices.
There are three ways a procedure can return data:
1) a result set (one or more) - the results of select statements (with the
exception of variable assigning selects);
2) output parameters - declared as output parameters when the procedure is
dreated and when the procedure is executed; and
3) the return value.
The latter is usually used for just the purpose of either returning 0 when
the procedure was executed without any errors, or returning a SQL Error
number (look at the sysmessages system table) or a user-defined numeric valu
e
that can be trapped in the calling process (e.g a user error could be the
fact that no rows were affected by an update statement).
But the essence of error-handling is IMHO:
1) to *prevent* possible errors (by validating inputs); and
2) to report fatal (unrecoverable) errors to the caller.
You mention the fact that you do most of your development work in .Net, so I
assume you are familiar with the TRY...CATCH method. If you need this
functionality, then you'd have to use SQL 2005, since it's not known in
previous versions.
What exactly is your goal here? We can help you find the best solution to
any problem, but we really need to know what it is that you need.
ML
p.s. the Zen model is the future. :)
http://milambda.blogspot.com/

error handling and transactions - speed question

I've been very carefully studying
http://www.sommarskog.se/error-handling-II.html recently. I went through it
some time ago before I was very familiar with working in SQL and it was all
sort of mysterious to me. Now I'm finding it to be very helpful.
Here's my issue right now: I have a former coworker who has been wroking in
sql 2k for several years now (we tend to disagree often on programming
techniques). When I asked him today how extensively he implements the sorts
of strategies detailed in Erland's articles he said that he rarely does any
of this. I was surprised. He said that occasionally and with great restraint
he'll use a begin/commit structure. He says that if you have a system with
many users then you have to worry about the system getting bogged down in
transactions. I mentioned the classic example of transferring money from a
savings account to a checking acount and what if something fails between the
2 update statements and he said that if you have a banking system where
thousands of people are hammering away at the system all day long then using
transactiosn in such a situation would be detrimental to performance. He
thinks that in theory using begin/commmit and @.@.error is very good but in
it's not very practical in the real world. I'm thinking that would be true
if you didn't design your error handling carefully as Erland outlines. I
would very much appreciate some feedback from the experts on this topic
because in my opinion, not handling errors at all is a bad idea. Is my
friend way off, somewhere in the middle or right on target?
Thanks,
KeithLet me guess, he's the type of guy who takes a chance when bicycling into a
crossing, and don't
break in order to not loose speed; hoping that no car will intersect?
Sure, if your customer (MD, president, or whatever) agrees that some crap in
the database is OK, you
don't have to worry (as much) about transaction handling. But make sure it i
s a conscious decision,
taken higher up in the organization and make sure you have this in writing (
or it will be your ass).
Another way of looking at a DBMS is as a state machine. Each modification ta
kes you from one state
to another state. With transactions, you can protect yourself from disallowe
d states.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith G Hicks" <krh@.comcast.net> wrote in message news:ugsl8asTGHA.1576@.tk2msftngp13.phx.g
bl...
> I've been very carefully studying
> http://www.sommarskog.se/error-handling-II.html recently. I went through i
t
> some time ago before I was very familiar with working in SQL and it was al
l
> sort of mysterious to me. Now I'm finding it to be very helpful.
> Here's my issue right now: I have a former coworker who has been wroking i
n
> sql 2k for several years now (we tend to disagree often on programming
> techniques). When I asked him today how extensively he implements the sort
s
> of strategies detailed in Erland's articles he said that he rarely does an
y
> of this. I was surprised. He said that occasionally and with great restrai
nt
> he'll use a begin/commit structure. He says that if you have a system with
> many users then you have to worry about the system getting bogged down in
> transactions. I mentioned the classic example of transferring money from a
> savings account to a checking acount and what if something fails between t
he
> 2 update statements and he said that if you have a banking system where
> thousands of people are hammering away at the system all day long then usi
ng
> transactiosn in such a situation would be detrimental to performance. He
> thinks that in theory using begin/commmit and @.@.error is very good but in
> it's not very practical in the real world. I'm thinking that would be true
> if you didn't design your error handling carefully as Erland outlines. I
> would very much appreciate some feedback from the experts on this topic
> because in my opinion, not handling errors at all is a bad idea. Is my
> friend way off, somewhere in the middle or right on target?
> Thanks,
> Keith
>|||geee. and he's a db programmer?
if he doesn't use begin/commit statments in multi statement stored procs he
eventually ends up with much bigger number of transactions than with these
statements. each statement is auto committed by default, unless he uses
"implicit transactions on" setting..
http://msdn2.microsoft.com/en-us/library/ms175523.aspx
Peter|||I completely agree with Tibor... and have NEVER written any serious
application without doing proper error handling... If the data is not
important - that's one thing... but if it is used to run your business, then
you have an obligation to make sure things are good...Yes, depending on the
work you are doing, transactions might cause some locking - that is the
INTENDED way they work... They prevent one persons work from overwriting and
interfering with another..
To presumptively say that trans cause to many problems is disappointing and
lazy.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"unknown" wrote:

>|||"Slow and right" beats "fast and wrong" two out of three times...
because there's always someone who doesn't care about the right answer, just
give me any answer fast.
I, for one, strongly agree with the other comments here, that good logical
transactional control and error handling is far more important than shaving
off a few milliseconds at the risk of a wrong answer.
btw, when I've dealt with folks who didn't see much value in certain SQL
technologies it was because they didn't really understand the technology -
like the data modeler who said that query path to the data didn't matter in
a database design, and sure enough, he couldn't write a basic query.
-Paul Nielsen
SQL Server MVP
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:ugsl8asTGHA.1576@.tk2msftngp13.phx.gbl...
> I've been very carefully studying
> http://www.sommarskog.se/error-handling-II.html recently. I went through
> it
> some time ago before I was very familiar with working in SQL and it was
> all
> sort of mysterious to me. Now I'm finding it to be very helpful.
> Here's my issue right now: I have a former coworker who has been wroking
> in
> sql 2k for several years now (we tend to disagree often on programming
> techniques). When I asked him today how extensively he implements the
> sorts
> of strategies detailed in Erland's articles he said that he rarely does
> any
> of this. I was surprised. He said that occasionally and with great
> restraint
> he'll use a begin/commit structure. He says that if you have a system with
> many users then you have to worry about the system getting bogged down in
> transactions. I mentioned the classic example of transferring money from a
> savings account to a checking acount and what if something fails between
> the
> 2 update statements and he said that if you have a banking system where
> thousands of people are hammering away at the system all day long then
> using
> transactiosn in such a situation would be detrimental to performance. He
> thinks that in theory using begin/commmit and @.@.error is very good but in
> it's not very practical in the real world. I'm thinking that would be true
> if you didn't design your error handling carefully as Erland outlines. I
> would very much appreciate some feedback from the experts on this topic
> because in my opinion, not handling errors at all is a bad idea. Is my
> friend way off, somewhere in the middle or right on target?
> Thanks,
> Keith
>