none
Conversion failed when converting from a character string to uniqueidentifier.

    Question

  • I have an issue where a UNIQUEIDENTIFIER field contains a NULL value.

    Essentially, I have a custom Scalar-Valued Function that accepts an NVARCHAR(50) parameter, and when attempting to use this function in a SELECT Statement I'm getting the error noted in the Title.

    Example:

    SELECT dbo.ListManual(CONVERT(NVARCHAR(50), ISNULL(LookupID, ''))) AS Listing FROM EnrolleeExtensionBase

    If the LookupID field (UNIQUEIDENTIFIER) contains a valid GUID, this works fine - however if it is NULL it is unable to do a conversion to a blank string.

    I have tried to use COALESCE as follows:

    SELECT COALESCE(CONVERT(NVARCHAR(50), LookupID), '') FROM EnrolleeExtensionBase

    Which, returns a wonderful valid result set of GUIDs and blanks.

    However, if I try to put the same logic as a parameter to my Function it again gives problems with converting:

    SELECT dbo.ListManual(COALESCE(CONVERT(NVARCHAR(50), LookupID), '')) AS Listing FROM EnrolleeExtensionBase

    Attempting to CAST or CONVERT the COALESCE results to a string does not have any effect on the resulting data type and still throws the same error with conversion.

    Basically, this seems to be an issue with attempting to convert a GUID field with possible blank values to a string as an inline process.

    Any ideas?

    Friday, February 25, 2011 5:24 PM

Answers

  • How about another guess.  Is there somewhere in your function that you convert the uniqueidentifier (either explicitly or implicitly - for example if you compare the nvarchar(50) parameter to a uniqueidentifier variable or column, SQL will attempt to convert the nvarchar to a uniqueidentifier (because uniqueidentifier has a higher precedence).  If that is true, you are not getting the error in the function call, but somewhere in the function.  For example, if I run

    Create Table dbo.FooTable(FooGuid uniqueidentifier default NewID());
    Insert dbo.FooTable(FooGuid) Default Values;
    Insert dbo.FooTable(FooGuid) Values (Null);
    go
    
    Create Function dbo.FooFunction (@Input nvarchar(50)) Returns nvarchar(50) As
    Begin
     Return @Input;
    End
    go
    
    Select FooGuid, dbo.FooFunction(COALESCE(CONVERT(NVARCHAR(50), FooGuid), '')) AS Listing 
    From dbo.FooTable;
    
    Select FooGuid, dbo.FooFunction(COALESCE(CONVERT(NVARCHAR(50), FooGuid), '')) AS Listing 
    From dbo.FooTable
    Where FooGuid = dbo.FooFunction(COALESCE(CONVERT(NVARCHAR(50), FooGuid), ''));
    
    
    go
    Drop Function dbo.FooFunction;
    Drop Table dbo.FooTable;
    

    the first select runs fine, but the second select gets a conversion error because the nvarchar(50) result of an empty string is implicitly converted in the WHERE clause.

    Tom

    • Marked as answer by KJian_ Thursday, March 03, 2011 7:17 AM
    Friday, February 25, 2011 6:04 PM

All replies

  • What is the datatype of the parameter declared in the dbo.ListManual procedure?  I suspect it is uniqueidentifier.  If so, your code converts the LookupID to a varchar, then converts the Null values to an empty string, then call the stored proc.  But to call the stored proc, if the parameter is uniqueidentifier, SQL must convert the varchar back to a uniqueidentifier, but it cannot convert an empty string to a uniqueidentifier, so you get that error. 

    I would recommend you just pass the LookupID without conversion to your stored proc, and rewrite the stored proc to handle Null values.

    Tom

    Friday, February 25, 2011 5:35 PM
  • The Data Type of the parameter is also NVARCHAR(50).

    Due to some design issues with the system being used, GUID fields often have foreign key fields in other tables that are of type NVARCHAR(50) - so a lot of converting needs to be done in order for JOINs to take place...  :(

    Friday, February 25, 2011 5:38 PM
  • Probably somewhere in the function itself the parameter is being converted, perhaps implicitly, to a UniqueIdentifier.   The error clearly states that that is what is happening somewhere in your code, and as Tom said, you can't convert a blank string to a UniqueIdentifier.

    If you need help handling nulls in your function, please post the function code.


    -Tab Alleman
    Friday, February 25, 2011 6:00 PM
  • How about another guess.  Is there somewhere in your function that you convert the uniqueidentifier (either explicitly or implicitly - for example if you compare the nvarchar(50) parameter to a uniqueidentifier variable or column, SQL will attempt to convert the nvarchar to a uniqueidentifier (because uniqueidentifier has a higher precedence).  If that is true, you are not getting the error in the function call, but somewhere in the function.  For example, if I run

    Create Table dbo.FooTable(FooGuid uniqueidentifier default NewID());
    Insert dbo.FooTable(FooGuid) Default Values;
    Insert dbo.FooTable(FooGuid) Values (Null);
    go
    
    Create Function dbo.FooFunction (@Input nvarchar(50)) Returns nvarchar(50) As
    Begin
     Return @Input;
    End
    go
    
    Select FooGuid, dbo.FooFunction(COALESCE(CONVERT(NVARCHAR(50), FooGuid), '')) AS Listing 
    From dbo.FooTable;
    
    Select FooGuid, dbo.FooFunction(COALESCE(CONVERT(NVARCHAR(50), FooGuid), '')) AS Listing 
    From dbo.FooTable
    Where FooGuid = dbo.FooFunction(COALESCE(CONVERT(NVARCHAR(50), FooGuid), ''));
    
    
    go
    Drop Function dbo.FooFunction;
    Drop Table dbo.FooTable;
    

    the first select runs fine, but the second select gets a conversion error because the nvarchar(50) result of an empty string is implicitly converted in the WHERE clause.

    Tom

    • Marked as answer by KJian_ Thursday, March 03, 2011 7:17 AM
    Friday, February 25, 2011 6:04 PM