Microsoft – SQL Server – IsGuid

Background

 

Talking about unfinished assignment, 2 1/2 years ago I posted about my joy that Microsoft now has “exception safe functions” for converting data
between different data types.

That post is here:

Microsoft – SQL Server – IsGuid Function
https://danieladeniji.wordpress.com/2012/06/23/microsoft-sql-server-isguid-function/

But, now that I need that sample code, I can not find it.

So let us quickly write one.

 

IsGuid – Does not Work


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[isGuid]') is null
begin

    exec('create function [dbo].[isGuid]() returns bit begin return 1/0 end')

end
go

ALTER function [dbo].[isGuid]
(
    @value varchar(255)
)
returns bit
WITH SCHEMABINDING
as 
begin 

        /*
            Error Message:
                MS SQL Server :- Version = 11.2 SP2
                Date          : 2014-11-7
                  
                   ( 
                       error message
                         pasted below
                   )
       
        */

        declare @valueID uniqueIdentifier
        declare @bValid bit

        --try_convert(@value, uniqueIdentifier)
        
        begin try

            set @valueID = cast(@value as uniqueIdentifier)

            set @bValid = 1

        end try
        begin catch

            set @bValid = 0
            
        end catch


        return (@bValid)
end

GO

Compilation Error


Image

IsGUIDCompilationError

 

 

 

IsGuid – Works

 



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[isGuid]') is null
begin

    exec('create function [dbo].[isGuid]() returns bit begin return 1/0 end')

end
go

ALTER function [dbo].[isGuid]
(
    @value varchar(255)
)
returns bit
WITH SCHEMABINDING
as 
begin 


        declare @bValid bit
        declare @valueID uniqueIdentifier


        /*
            If we attempt to use try_parse, we get Operand type clash: int 
                  is incompatible with uniqueidentifier


            --http://msdn.microsoft.com/en-us/library/hh213126.aspx
            Msg 206, Level 16, State 2, Procedure isGuid, Line 50
            Operand type clash: int is incompatible with uniqueidentifier
        */
        --set @valueID = try_parse(@value as uniqueIdentifier)

        /*
            http://msdn.microsoft.com/en-us/library/hh230993.aspx
        */
        set @valueID = try_convert(uniqueIdentifier, @value)

        if (@valueID is null)
        begin
            set @bValid = 0
        end
        else
        begin
            set @bValid = 1
        end

        return (@bValid)

end

GO

Summary

In conclusion, we can not use the classic try/catch exception handling inside of User defined functions.

Also, unfortunately try_Parse does not accept the “UniqueIdentifier” as a datatype.

But, it seems that we are good with try_convert.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s