Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TRY CATCH on a CONVERT in a Select Statement

Is it possible to use TRY CATCH blocks in SQL Selects?

For stuff similar to this for example:

select 
   order, 
   CONVERT(DATETIME, orderDate)
from orders

What's the best way of handling this scenario?

like image 591
JohnIdol Avatar asked May 28 '09 13:05

JohnIdol


People also ask

Can we use try catch in SQL functions?

Note that you cannot use TRY... CATCH blocks inside T-SQL UDFs. If you have to capture errors that occur inside a UDF, you can do that in the calling procedure or code.

How do you try catch in SQL?

A TRY... CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. A TRY block must be immediately followed by an associated CATCH block. Including any other statements between the END TRY and BEGIN CATCH statements generates a syntax error.

Can we use try catch in stored procedure?

In the scope of a TRY / CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed: ERROR_NUMBER() returns the number of the error. ERROR_SEVERITY() returns the severity. ERROR_STATE() returns the error state number.

How do you handle exceptions in SQL query?

To handle exception in Sql Server we have TRY.. CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks.


4 Answers

I don't know about try-catch, but in SQL Server you have the ISDATE function and can there for do something like

CASE WHEN ISDATE(orderDate) = 1 THEN CONVERT(DateTime, orderDate) ELSE GETDATE() END
like image 148
Robin Day Avatar answered Oct 14 '22 08:10

Robin Day


In MS SQL Server 2012 there is a new construct that does exactly what is asked for:


SELECT 
    CASE WHEN TRY_CONVERT(float, 'test') IS NULL 
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
END AS Result;
    GO

See also http://msdn.microsoft.com/en-us/library/hh230993.aspx

like image 41
user10633 Avatar answered Oct 14 '22 10:10

user10633


In the SELECT clause itself, no.

You can test for a date though using ISDATE()

select 
   order, 
   CASE WHEN ISDATE(orderDate) = 1 THEN CONVERT(DATETIME, orderDate) ELSE NULL END
from orders
like image 31
gbn Avatar answered Oct 14 '22 08:10

gbn


I don't think a try catch is possible inside a select, but outside is possible when you're working with stored procedures.

begin try
    select cast(strartnr as int) from table
end try
begin catch 
    select 10000 from table
end catch
like image 42
freggel Avatar answered Oct 14 '22 09:10

freggel