Re: RAISERROR Behaviour

From: Paul Hatcher (phatcher_at_spamless.cix.co.uk)
Date: 05/01/04 

Date: Sat, 1 May 2004 10:45:59 +0100

Dan

Thanks for that - I wasn't acquiring the return code of the called stored
procedure, i.e. I was treating RAISERROR as if it raised an exception and an
error number.

Regards

Paul

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:eDD2g0RLEHA.644@tk2msftngp13.phx.gbl...
> To add to the response by Vyas, here's an example that returns the error
> number to the calling proc:
>
> CREATE PROC B
> @P1 int,
> @P2 int
> AS
> DECLARE @Result int, @LastError int
> SET @Result = @P1/@P2
> SELECT @LastError = @@ERROR
> IF @LastError <> 0 GOTO Done
> --do other stuff
> Done:
> RETURN @LastError
> GO
>
> CREATE PROC A
> AS
> DECLARE @ReturnCode int, @Msg varchar(100)
> EXEC @ReturnCode = B 1, 0
> IF @ReturnCode <> 0
> BEGIN
> SET @Msg = 'B Failed'
> RAISERROR (@Msg, 16, 1)
> END
> GO
>
> EXEC A
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Paul Hatcher" <phatcher@spamless.cix.co.uk> wrote in message
> news:eOmN6cRLEHA.268@TK2MSFTNGP10.phx.gbl...
> > I have two SPs A and B, with A calling B. What I want is for A to stop
> > processing if the call to B fails so I have code in A like
> >
> > EXEC dbo.B @P1, @P2
> > IF @@ERROR<> 0
> > BEGIN
> > SET @Msg = 'B Failed'
> > RAISERROR @Msg, 16, 1
> > END
> >
> > The problem is that B is raising it's error (same as above severity 16)
> but
> > @@ERROR is reporting 0 and so A does not terminate - what am I doing
> wrong?
> >
> > Regards
> >
> > Paul
> >
> >
>
>

Relevant Pages

  • Re: Assigns in Initializer
    ... PROC Catch_startup_assign(rucb, passthru, message, msglen, match) ... INT .passthru; ... I am using the INITIALIZER guardian call with assignproc, ...
    (comp.sys.tandem)
  • Re: New list commands ... What do you think ? (LONG)
    ... static int ... int itemc, i, cmdLen, newObjc; ... proc prod2 list { ... proc map-helper {proc accum item} { ...
    (comp.lang.tcl)
  • Re: Getting Return Value From Stored Proccedure (Part 2)
    ... for a proc parameter to return a value to the caller, the proc must declare it as a output parameter as sql defaults to pass by value. ... create procdure test1 @i1 int, ... Dim conn As New SqlConnection ... Dim param As New SqlParameter ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: NEED Query to Find out all Databases sizes
    ... I thought CalcSpace was a cool proc, so just for kicks, I thought I would ... -- select * from #tblSize order by rows desc ... int)),sum-1) as int) + ... > I want to find out the SQL databases total size in SQL 2000 server, ...
    (microsoft.public.sqlserver.server)
  • Re: Help with writing a sproc
    ... UserID, RoleID? ... Create Proc sproc ... select table1.userId, UserName, UserRole=RoleName ... Create Proc sproc (@userID int) ...
    (microsoft.public.sqlserver.fulltext)