SQL statement erroring out due to source field being unidqueidentifier

In your ISNULL statement, you are building an empty string if the column is NULL. That is not valid for a UNIQUEIDENTIFIER data type.

DECLARE @t UNIQUEIDENTIFIER = ''

This will return the same error that you are seeing.

Quick suggestion, use a pseudo null value for the uniqueidentifier (00000000-0000-0000-0000-000000000000), implement it by replacing the empty string in the ISNULL function to that value.

😎

,ISNULL(Convert(nvarchar(50),OPP1.OriginatingLeadId),'00000000-0000-0000-0000-000000000000') AS 'OriginatingLeadId'

If you need to get the null in a select where it's been replaced by the pseudo value use the NULLIF function

,NULLIF(CONVERT(VARCHAR(50),OriginatingLeadId,0),'00000000-0000-0000-0000-000000000000')

That shouldn't be an issue here should it ? (unless I am missing something 🙂 )

SELECT ISNULL(Convert(nvarchar(50), CONVERT(uniqueidentifier, NULL)),'')

AS 'OriginatingLeadId'

Until one tries to inert the empty string into the uniqueidentifier column;-)

😎

Indeed :-), and that is actually what the error message is saying of course:

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

Slightly begs the question why the NULL cannot be left there and the Presentation APP/whatever deal with displaying "NULL as blank string" ...

... and a perennial problem when converting numbers, dates, GUIDs, etc. to strings and then assuming that the downstream APP/usage will be able to successfully parse them back again ...

Took some help from a friend but the answer turned out to be simple

isnull(cast([OriginatingLeadId] as VARCHAR(36)),'NA')

when checking for NULL in a uniqueidentifier field you have to cast it as VARCHAR and you must use the 36 length to match up to the original field size. worked like a charm.

I can't see how that would behave any differently to your original

ISNULL(Convert(nvarchar(50),OPP1.OriginatingLeadId),'') AS 'OriginatingLeadId'

I did wonder when I saw the original if there was any point in the Nvarchar, rather than Varchar, but I can't see it having the side effect you mention.

Ditto 36 vs. 50 width, or '' blank string vs. 'NA' string.

But I'd be very interested to hear how any of those could constitute a difference.

not sure I am technical enough to explain it correctly, but as it was explained to me, the ISNULL answer takes on the attributes of the original field. In this instance uniqueidentier, which is very specific field type. In order replace the NULL value with NA I had originally done this

isnull(cast([OriginatingLeadId] as VARCHAR(2)),'NA') (yes I know not my original post, but I had changed it after the post and some additional research)

I had set the VARCHAR to 2. because I only wanted to add NA. But not thinking, for those times when the field had a value, it of course would be a 36 character value. So the simple answer was to set it to VARCHAR to allow NA value, but to set the column width to 36 to allow for the original field length.

I hope that helps.

Yup, I can see where you are coming from. But your code (originally posted here, maybe the code you were working on was somewhat different) had

ISNULL(CONVERT(nvarchar(50), YourGUID) ...

ISNULL is going to see the first parameter as being an NVarchar(50), and not a GUID, hence why I was questioning why the change made any difference.

ISNULL(CAST YourGUID AS NVarchar(50))

is equally going to see the first parameter as being an NVarchar(50), and not a GUID, so the two are interchangeable in that sense.

My concern is that the fix you have found is not actually in this statement, but somewhere else, and thus may only be serendipity, hence why I was questioning it.

However, I'm pleased you've got it going 🙂 just anxious in case the fix is not the final fix!