Geek Answers Handbook

How to suppress or ignore errors in an SQL SELECT statement

Does anyone know how to suppress / ignore errors in a select statement?

My select statement uses the STLineFromText function, and when it hits an invalid line, it throws an error. I'm fine, ignoring bad lines and can't change the source data.

Here is an example request that demonstrates my problem:

SELECT geography::STLineFromText('LINESTRING(-74.2204037952351 40.4283173372472,-74.2204851952350 40.4283519372471)', 4326) UNION ALL
SELECT geography::STLineFromText('LINESTRING(-74.2316367952177 40.4386102038979,-74.2313671952181 40.4388540705641)', 4326) UNION ALL
SELECT geography::STLineFromText('LINESTRING(-74.2229282618978 40.4252709372519,-74.2229171285645 40.4252638039186,-74.2229282618978 40.4252709372519,-74.2227441952315 40.4251499372521,-74.2231121285642 40.4243291372534)', 4326) UNION ALL
SELECT geography::STLineFromText('LINESTRING(-74.2418989952017 40.4417621372263,-74.2417773285352 40.4417915372263)', 4326) UNION ALL
SELECT geography::STLineFromText('LINESTRING(-74.2166069952410 40.4334496039059,-74.2158269952422 40.4336396039056)', 4326)

and here is the error:

Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.ArgumentException: 24200: The specified input does not represent a valid geography instance.
System.ArgumentException: 
   at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

The ideal situation is to simply return # 1, # 2, # 4 and # 5, ignoring # 3

Thank!

+5
source share
2 answers

So, I removed the bullet and wrote my own CLR function so that I can enable try / catch. It works very well.

<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function STLineFromTextFlexible(ByVal LineString As SqlChars, ByVal SRID As Integer) As Microsoft.SqlServer.Types.SqlGeography
    Try
        Dim Geo As New SqlGeography()

        Geo = SqlGeography.STLineFromText(LineString, SRID)

        Return Geo
    Catch ex As Exception
        Return Nothing
    End Try
End Function

Credits:

http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.80).aspx

http://msdn.microsoft.com/en-us/library/ms131065.aspx

+3

; , "" SQL Server. - UDF, , STLineFromText, try/catch null .

, , try/catch . UDF, OPENQUERY , . .

- ...

CREATE PROCEDURE SP_IgnoreErrors(@value varchar(255), @param int)
AS
BEGIN
    BEGIN TRY
        SELECT geography::STLineFromText(@value, @param) AS VALUE
    END TRY
    BEGIN CATCH
        SELECT NULL AS VALUE
    END CATCH
END

CREATE FUNCTION IgnoreErrors(@value varchar(255), @param int) RETURNS XXX
AS
BEGIN
    DECLARE @output XXX

    SELECT @output = VALUE from OPENQUERY([YOURINSTANCE],'Database.dbo.SP_IgnoreErrors ''' + @value + ''', ' + convert(varchar, @param))

    RETURN @output
END

, , . SSMS , , , , , , .

+1

More articles:


All Articles