how to get an error message and save it in a table in sybase?

Question

drop procedure getTitle

create procedure getTitle @title_id VARCHAR(9)
as

declare @error  int,
@title varchar(100)

begin

insert into tab1 (CODE) VALUES (1) jhghjghj --- the jhghjghj  places to cause an error

SELECT @title = description from master..sysmessages where error = @@error
update ex_employee set info = @title
commit
print "%1!", @title
end
go

If I try compile this procedure it will give me this error

Incorrect syntax near 'jhghjghj'.

What I want is to save this message Incorrect syntax near 'jhghjghj'. into a table. I am trying to catch it in a way . Is that possible ?

Answer 1

Here is the solution. It is little tricky. Make the query dynamic, so that Syabse wont complain about the syntax error while compile. You compilation will be fine and you will get your desire result in run-time.

create procedure getTitle @title_id VARCHAR(9)
as

declare @error  int,
@title varchar(100)

begin

declare @my_query varchar(500)
select @my_query = "insert into tab1 (CODE) VALUES (1) jhghjghj"  
exec (@my_query )

SELECT @title = description from master..sysmessages where error = @@error
update ex_employee set info = @title
commit
print "%1!", @title
end
go