how to capture custom made error message into table

Dear all,
I want to know how to custom made error message into table, as I illustrated
T-SQL below :
if objectproperty(object_id('DateTable'),'I
sUserTable')=1
drop table DateTable
if objectproperty(object_id('InputFromFlatF
ile1'),'IsUserTable')=1
drop table InputFromFlatFile1
if objectproperty(object_id('errorLog'),'Is
UserTable')=1
drop table errorLog
if objectproperty(object_id('usp_testData')
,'IsProcedure')=1
drop proc usp_testData
create table DateTable
(datetimestamp datetime)
go
create table errorLog
(
data varchar(200),
errmsg varchar(2000)
)
go
create proc usp_testData
(@.dateinfo datetime)
as
begin
if convert(varchar(8),@.dateinfo,112)<'20060328'
begin
raiserror('Date input invalid because it''s entered before 28 Mar
2006',16,1)
return
end
else
insert into DateTable (datetimestamp) values (@.dateinfo)
end
go
create table InputFromFlatFile1
(
id int identity (1,1) not null,
linestring varchar(100)
)
go
insert into InputFromFlatFile1 (linestring) values ('20060328');
insert into InputFromFlatFile1 (linestring) values ('20060212');
insert into InputFromFlatFile1 (linestring) values ('20060115');
declare @.linestring varchar(200), @.errmsg varchar(2000)
declare @.cnt int, @.recnum int, @.error int
set @.cnt = 1
select @.recnum = count(*) from InputFromFlatFile1
while @.cnt <= @.recnum
begin
select @.linestring = linestring from InputFromFlatFile1 where id = @.cnt;
print @.linestring
exec @.error = usp_testData @.linestring;
if @.error<>0 or @.@.error <> 0
begin
if @.@.error <> 0
begin
set @.error = @.@.error
select @.errmsg = description from master.dbo.sysmessages where error =
@.error
end
/*
How can I capture user-made error in stored procedure usp_testData to put
into ErrorLog table
'
*/
insert into errorLog values (@.linestring, @.errmsg)
end
set @.cnt = @.cnt + 1;
end
select * from DateTable
select * from errorLog
from query analyzer I get error message below :
Date input invalid because it is entered before 28 Mar 2006
but I cannot get the error message as above in errorLog table
How can I capture error message like in query analyzer in errorLog table?
Regards,
Koronx
SQL HobbistHi
Check out http://www.sommarskog.se/error-handling-II.html and
http://www.sommarskog.se/error-handling-I.html You will need to add the
insert statement into your code or possibly do it through the client
application by trapping the error message returned.
John
"Kornx Koronx" wrote:

> Dear all,
> I want to know how to custom made error message into table, as I illustrat
ed
> T-SQL below :
> if objectproperty(object_id('DateTable'),'I
sUserTable')=1
> drop table DateTable
> if objectproperty(object_id('InputFromFlatF
ile1'),'IsUserTable')=1
> drop table InputFromFlatFile1
> if objectproperty(object_id('errorLog'),'Is
UserTable')=1
> drop table errorLog
> if objectproperty(object_id('usp_testData')
,'IsProcedure')=1
> drop proc usp_testData
> create table DateTable
> (datetimestamp datetime)
> go
> create table errorLog
> (
> data varchar(200),
> errmsg varchar(2000)
> )
> go
> create proc usp_testData
> (@.dateinfo datetime)
> as
> begin
> if convert(varchar(8),@.dateinfo,112)<'20060328'
> begin
> raiserror('Date input invalid because it''s entered before 28 Mar
> 2006',16,1)
> return
> end
> else
> insert into DateTable (datetimestamp) values (@.dateinfo)
> end
> go
> create table InputFromFlatFile1
> (
> id int identity (1,1) not null,
> linestring varchar(100)
> )
> go
> insert into InputFromFlatFile1 (linestring) values ('20060328');
> insert into InputFromFlatFile1 (linestring) values ('20060212');
> insert into InputFromFlatFile1 (linestring) values ('20060115');
> declare @.linestring varchar(200), @.errmsg varchar(2000)
> declare @.cnt int, @.recnum int, @.error int
> set @.cnt = 1
> select @.recnum = count(*) from InputFromFlatFile1
> while @.cnt <= @.recnum
> begin
> select @.linestring = linestring from InputFromFlatFile1 where id = @.cnt;
> print @.linestring
> exec @.error = usp_testData @.linestring;
> if @.error<>0 or @.@.error <> 0
> begin
> if @.@.error <> 0
> begin
> set @.error = @.@.error
> select @.errmsg = description from master.dbo.sysmessages where error =
> @.error
> end
> /*
> How can I capture user-made error in stored procedure usp_testData to put
> into ErrorLog table
> '
> */
> insert into errorLog values (@.linestring, @.errmsg)
> end
> set @.cnt = @.cnt + 1;
> end
> select * from DateTable
> select * from errorLog
> from query analyzer I get error message below :
> Date input invalid because it is entered before 28 Mar 2006
>
> but I cannot get the error message as above in errorLog table
> How can I capture error message like in query analyzer in errorLog table?
> Regards,
> Koronx
> SQL Hobbist