Handling data type conversion errors in TSQL

Handling data type conversion errors in TSQL

Post by Michal Raat » Wed, 17 Jul 2002 15:39:39



Hello everyone!
I have a problem with converting datatypes in TSQL; I have function with
such statement:




string) I get an error message and function fails.

to make it (and haw to awoid error message of course).

I have looked in books online and in MSDN and found no solution.

(in Delphi it was for example
  try
   {do something}
 except
  {do something}
 end
)

thank you for any help in advance
michal

 
 
 

Handling data type conversion errors in TSQL

Post by Greg Obleshchu » Wed, 17 Jul 2002 15:45:59


Michal
try this



else

--
I hope this helps
regards
Greg O MCSD
Document any SQL server database
AGS SQL Scribe http://www.ag-software.com/ags_scribe_index.asp


> Hello everyone!
> I have a problem with converting datatypes in TSQL; I have function with
> such statement:




> string) I get an error message and function fails.

How
> to make it (and haw to awoid error message of course).

> I have looked in books online and in MSDN and found no solution.

> (in Delphi it was for example
>   try
>    {do something}
>  except
>   {do something}
>  end
> )

> thank you for any help in advance
> michal


 
 
 

Handling data type conversion errors in TSQL

Post by Greg Linwoo » Wed, 17 Jul 2002 16:05:28


Hi Michal


as some cause fatal errors.

One example is converting an invalid string to a date-time variable:



In this (albeit extreme) example, TSQL execution is terminated
unconditionally by MS SQL and the error is thrown through whatever API

no-where as that code will never run.

Unfortunately - at this stage T-SQL does not have any structured exception
handling such as your Delphi example, but we are hearing noises that this
will be included in the next MS SQL release. For now, if you want to design
robust exception handling into an MS SQL based app, you have little choice
but to do it in the client.

Defensive T-SQL coding is a worth-while practice but will only get you part
way there.

HTH

Cheers,
Greg Linwood


> Hello everyone!
> I have a problem with converting datatypes in TSQL; I have function with
> such statement:




> string) I get an error message and function fails.

How
> to make it (and haw to awoid error message of course).

> I have looked in books online and in MSDN and found no solution.

> (in Delphi it was for example
>   try
>    {do something}
>  except
>   {do something}
>  end
> )

> thank you for any help in advance
> michal

 
 
 

Handling data type conversion errors in TSQL

Post by Michal Raat » Wed, 17 Jul 2002 17:40:15


great!

IsNumeric returns 1... I managed to make something like this:



 ELSE

and now it's all OK.
thank you very much for help.
regards
Michal



> Michal
> try this



> else

> --
> I hope this helps
> regards
> Greg O MCSD
> Document any SQL server database
> AGS SQL Scribe http://www.ag-software.com/ags_scribe_index.asp



> > Hello everyone!
> > I have a problem with converting datatypes in TSQL; I have function with
> > such statement:



"123"

non-numeric
> > string) I get an error message and function fails.

> How
> > to make it (and haw to awoid error message of course).

> > I have looked in books online and in MSDN and found no solution.

> > (in Delphi it was for example
> >   try
> >    {do something}
> >  except
> >   {do something}
> >  end
> > )

> > thank you for any help in advance
> > michal

 
 
 

Handling data type conversion errors in TSQL

Post by Michal Raat » Wed, 17 Jul 2002 17:44:48


Thank you for the answer.

I see that the "real" error handling can be made at the client side only.
But I have to prepare a trigger and i have to forget it...

I have used an IsNumeric function,(as Greg Obleshchuk suggested in the
previous reply) and now all is OK.

Regards
Michal



> Hi Michal


error
> as some cause fatal errors.

> One example is converting an invalid string to a date-time variable:



> In this (albeit extreme) example, TSQL execution is terminated
> unconditionally by MS SQL and the error is thrown through whatever API

> no-where as that code will never run.

> Unfortunately - at this stage T-SQL does not have any structured exception
> handling such as your Delphi example, but we are hearing noises that this
> will be included in the next MS SQL release. For now, if you want to
design
> robust exception handling into an MS SQL based app, you have little choice
> but to do it in the client.

> Defensive T-SQL coding is a worth-while practice but will only get you
part
> way there.

> HTH

> Cheers,
> Greg Linwood



> > Hello everyone!
> > I have a problem with converting datatypes in TSQL; I have function with
> > such statement:



"123"

non-numeric
> > string) I get an error message and function fails.

> How
> > to make it (and haw to awoid error message of course).

> > I have looked in books online and in MSDN and found no solution.

> > (in Delphi it was for example
> >   try
> >    {do something}
> >  except
> >   {do something}
> >  end
> > )

> > thank you for any help in advance
> > michal

 
 
 

1. Data conversion problems handling DB2 Decimal data types

Greetings:

Has anyone had any experience handling DB2 SQL data type DECIMAL (PIC
S9(precision)V9(scale) COMP-3) in stored procedures via the
java.sql.CallableStatement.  We are currently exeriencing a problem
calling a stored procedure where our DB2 DECIMAL data is "rounding"
improperly or losing decimal positions.  (i.e. 9.99 becomes
99.89999999999999) If anyone has any insight please respond.  Thanks

2. Drop database marked inaccessable.

3. Data type conversion error when saving numeric data types

4. Deploying ADO Data bound controls / changing connection string

5. error handling in sql with types conversion

6. ODBC timeout expired error - catastrophic failure

7. conversion of ODBC SQL data types to ODBC C data types

8. Disallowed implicit conversion from data type nvarchar to data type money

9. SQL-Procedure called with ODBC terminates on error and ignores TSQL-error handling

10. Scheduled job creates data type conversion error, executing it is OK

11. View to an Oracle Table data type conversion error