COALESCE(NULLIF(@intError, 0), @@ERROR)

Anybody know if the COALESCE(NULLIF ...)...) can raise error itself, and if
it safely to use this combination of checking error status
Every time after i calling to some store procedure i use this:
---
DECLARE @.intError int
EXEC @.intError = myProcedure @.param1, @.param2 ....
SELECT @.intError = COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
IF(@.intError =0 )
.....
.....
---
my question is, can the @.@.ERROR variable have an error that COALESCE or
NULLIF function were raise?
Thanks.
P.S. if it's not good idea to use it after calling to stored procedures so
what can u advice me
Message posted via http://www.webservertalk.comHi
I'm not sure I understand you.
Why not just doing the following?
create proc myproc
@.par int
as
--do something here
if @.@.error <>0
return -1
else
resturn 1
go
declare @.err int
select @.err=exec myproc @.par
if @.err =-1
raiserror ('It was an error',16,1)
Read this great article
http://www.sommarskog.se/error-handling-I.html
"E B via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:dc61838570f642498321c727d4c5742b@.SQ
webservertalk.com...
> Anybody know if the COALESCE(NULLIF ...)...) can raise error itself, and
if
> it safely to use this combination of checking error status
> Every time after i calling to some store procedure i use this:
> ---
> DECLARE @.intError int
> EXEC @.intError = myProcedure @.param1, @.param2 ....
> SELECT @.intError = COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
> IF(@.intError =0 )
> .....
> .....
> ---
> my question is, can the @.@.ERROR variable have an error that COALESCE or
> NULLIF function were raise?
> Thanks.
> P.S. if it's not good idea to use it after calling to stored procedures so
> what can u advice me
> --
> Message posted via http://www.webservertalk.com|||However my question is, can the COALESCE or NULLIF functions change the
status of @.@.ERROR variable
Message posted via http://www.webservertalk.com|||Any statement *could* result in an error but that looks pretty unlikely
in this case. Your code is about as safe as any other error-handling
code can be. In general keep your error handling code as simple as
possible.
David Portas
SQL Server MVP
--|||so is it good idea to use COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
after caling to some stored procedure or function in sql
Thanks
Message posted via http://www.webservertalk.com|||what the way i need to check if any error occured after calling to stored
procedure?
Message posted via http://www.webservertalk.com|||An SP won't actually return a value of NULL but if the SP can't be run (mayb
e
it doesn't exist or you don't have EXEC permissions) then the result leaves
the value of @.interror unaffected (NULL in your case). In that situation you
r
code will assign the error code to @.interror, which seems reasonable enough.
David Portas
SQL Server MVP
--
"E B via webservertalk.com" wrote:

> so is it good idea to use COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
> after caling to some stored procedure or function in sql
> Thanks
> --
> Message posted via http://www.webservertalk.com
>|||There is no error event in TSQL so the only way is to check the @.@.ERROR valu
e
after EVERY statement. This doesn't catch all errors though (see the article
that Uri posted). A system I'm using is to put error-handling in its own pro
c
and call that with @.@.ERROR as a parameter:
EXEC @.err = usp_error_handler @.@.ERROR, @.calling_proc, @.user_id
the proc redturns the @.@.ERROR value. If @.@.ERROR is zero the proc just
returns immediately without executing the handling code.
That's reasonable for processes that are long running but maybe not an
overhead you'll want in an SP that's called frequently. TSQL in SQL Server
2000 provides very little scope for good error handling and much of the time
you may find it easier and better to catch errors in your calling code in VB
/ C# or whatever.
David Portas
SQL Server MVP
--