error handling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

error handling

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-08 : 05:53:33

Hi,
This is briefly what I have in my stored proc.

create procedure uspTest

as

begin try

set nocount on

...
if (select count(*) from tblMain) > 0
begin
--some processing...
end

else
begin
-------?????----------------------------
--to shown an error message i.e. no data found.
-- how is this raised to the user?
-------?????----------------------------
end

set no count off

end try

begin catch

declare @msg nvarchar(200)
SET @msg = ('Error during xxx process...')

EXECUTE uspError @msg

end catch

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-08 : 05:56:46

Is there a question here?
My error handling is usually
begin catch
declare @ErrDesc varchar(max) = coalesce(ERROR_MESSAGE(),'<null>')
declare @ErrProc varchar(500) = coalesce(ERROR_PROCEDURE(),'<null>')
declare @ErrLine varchar(100) = coalesce(ERROR_LINE(),'<null>')
declare @ErrorNo varchar(100) = coalesce(ERROR_NUMBER(),'<null>')

insert Trace (Entity, key1, data1, data2)
select Entity = @entity, key1 = 'Failure',
data1 = '<ErrProc=' + @ErrProc + '>'
+ '<ErrLine=' + @ErrLine + '>'
+ '<ErrDesc=' + @ErrDesc + '>'
, data2 = '<SQLErrorNo=' + @ErrorNo + '>'
raiserror('Failed %s', 16, -1, @ErrDesc)
end catch

@entity has already been set to OBJECT_NAME(@@procid)

but it's up to you

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-08 : 06:32:56

-------?????----------------------------
--to shown an error message i.e. no data found.
-- how is this raised to the user?
-------?????----------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 06:50:00

quote:
Originally posted by arkiboys

-------?????----------------------------
--to shown an error message i.e. no data found.
-- how is this raised to the user?
-------?????----------------------------

capture @msg value as a output parameter and show it in your application

http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-08 : 07:02:39

You could just do a raiserror and it will be caught be the try catch block. Is it an error or just information though.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-08 : 07:19:26

quote:
Originally posted by nigelrivett

You could just do a raiserror and it will be caught be the try catch block. Is it an error or just information though.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

It is just information

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources