Capturing Data Type Mismatch

Hi,
Create Table tb_mismatch
(x int)
Create Procedure proc_mismatch
as
begin
insert into tb_mismatch values('s')
if @.@.error<>0
begin
print ' entered error loop'
end
print 'successfully exited'
end
exec proc_mismatch --executing the proc
Now, when i try to capture the above error its not getting trapped..its directly going to the final end statement.
I have even tried calling subprocedures so that it comes out of the inner procedure and by some means i can move forward in the outer proc,but even that failed.
The proc. is able to capture all the other errors like primary key violation,binary data truncated etc but not the datatype mismatch error (mainly int with varchar...)
any ideas are highly appreciated.
Thanks & regards,
Pavan.It looks like a little data checking is needed somewhere. Here is one example:

Create Table dbo.tb_mismatch
(x int)


Create Procedure dbo.proc_mismatch @.var varchar(50), @.err int OUTPUT
as

if isnumeric(@.var) = 1
BEGIN
insert into dbo.tb_mismatch (x) values(@.Var)
END
ELSE
BEGIN
set @.err = -1
END


if @.err<>0
begin
print 'encountered error'
end
else
begin
print 'successfully exited'
end



declare @.var varchar(50), @.errreturn int
set @.var = 's'
set @.errreturn = 0

exec dbo.proc_mismatch @.var, @.errreturn OUTPUT --executing the proc
select @.errreturn

drop proc dbo.proc_mismatch
drop table dbo.tb_mismatch|||Thanks for your quick response but my requirement is:
I have several update and insert statements in my actual procedure which fetches the data from an oracle DB and updates the sql database.. during these updates and inserts Business wants me to capture all the system related errors and when i am trying to capture the data mismatch error(manually placing a varchar value in a float field) the cursor is directly moving to the end of procedure,instead of populating the log file.
I dont think placing isnumeric for all int and float fields is the feasible solution,
any other ways??

Many Thanks
Pavan.|||No

And this sounds like a batch process...

I woul unload the data from oracle, bcp the data in to sql server, perform my audits, then load the data in|||Sounds good but it doesn't help my requirement as i have lots of validations to be done before performing any transactions and even need to Rollback transactions in some cases..
Do we have any exception handling mechanism to handle this ..other than raiseerror as it didn't worked out.. or is this a bug in sqlserver?? like we have when VALUE_ERROR exception in oracle|||SQL Server error handling is kludgey in 2000. SQL 2005, takes for steps to address that, but I haven't looked in to it.

So why can't you do basic aduitng in batches in a set based manner? What's the difficulty. You will need some staging tables, bit so what?

You need to divorce yourself from sequential cursor processing that you're accostomed too in Oracle...even in Oracle, it is over used a lot of times.

Good Luck.

If you continue to do it this way, create a second stored procedure that gets called...like a nested stored procedure...when the nested proc fails, it will rais out to the calling stored procedure, and the driver can then handle the error...but that's the long way around the mountain|||Brett,
thanks for ur concern.
I have tried the second option but it hasn't helped me out.
My code goes something like this..
gets the jobnumber and its related info from the oracle job master table..checks for its existance in sql db and then creating 2 cursors for diff tables checks and then lots of if's and else's,calculations..and once it goes through all the validations we will start inserting the details into some tables,move data to history and then update the main job table..if it fails in any of the case just rollback the whole operations..now the turn of next job comes into picture..
As of now it works fine until we dont get varied data from oracledb which has the similar db structure of sql server.
My scope is till its developed.|||I still don't know why you can't do something like this

USE Northwind
GO

-- Set up the situation
SET NOCOUNT ON
CREATE TABLE ORACLE_TABLE(Col1 varchar(10))
CREATE TABLE SQL_TABLE(Col1 int)
GO

-- Create some sample Data

INSERT INTO ORACLE_TABLE(Col1)
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'd'
GO

-- Report On Bad Data

SELECT * FROM ORACLE_TABLE WHERE ISNUMERIC(Col1) = 0

-- Place the good data in to SQL
INSERT INTO SQL_TABLE(Col1)
SELECT (Col1) FROM ORACLE_TABLE WHERE ISNUMERIC(Col1) = 1
GO

SELECT * FROM SQL_TABLE
GO

SET NOCOUNT OFF
DROP TABLE ORACLE_TABLE, SQL_TABLE
GO|||Try doing the same with a small change,changing the datatype from varchar to int,as this is my current structure,without using isnumeric option as my table has lots of columns and there are lots of insert and update statements.
CREATE TABLE ORACLE_TABLE(Col1 int)

Came to know that this error cannot be captured by sqlserver 2000 which is resolved in the next version sqlserver 2005 using the try catch block.