October 11, 2007 at 11:02 am
I searched the archives, but I was unable to find any discussion on trapping Msg 8114 when executing a SP that has been passed incorrect data type parameters.
I tried the error trapping strategy found in the article
An Error Handling Template for 2005 (http://www.sqlservercentral.com/articles/Development/anerrorhandlingtemplatefor2005/2295/)
but the strategy does not handle errors in data types being passed to the SP.
Is it possible to trap this type of error?
October 11, 2007 at 11:58 am
I'm not sure I have the precise answer, but I was just reading how certain errors can't be trapped at the moment of execution, but can be at a level of seperation. So, are you attempting to capture the incorrect data type error within the stored procedure? If so, no, it won't work. If you're trying to trap it outside the proc though...
Take this code:
CREATE PROCEDURE x
(@myparm int)
AS
SELECT @myparm AS MyParm
GO
BEGIN Try
EXEC x 'a'
END TRY
BEGIN CATCH
PRINT 'error'
END CATCH
I can catch the bad parameter error outside the proc. Does that help?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 11, 2007 at 12:09 pm
no it's not trappable, because it's an error raised BEFORE it enters the procedure, because it's a syntax and parameter checking error.
all sql statements are at least evaluated prior to execution, and this kind of error is raised in that situation.
this example raises the exact same issue:
create procedure pr_test (@param1 int,@param2 varchar(30),@param3 int)
as
begin
select 'nothing to see here, move along'
end
exec pr_test 'blah','blah','blah'
Server: Msg 8114, Level 16, State 4, Procedure pr_test, Line 0
Error converting data type varchar to int.
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply