Conversion Failed When Converting from a Character String to Uniqueidentifier

Conversion failed when converting from a character string to uniqueidentifier - Two GUIDs

The problem was that the ID column wasn't getting any value. I saw on @Martin Smith SQL Fiddle that he declared the ID column with DEFAULT newid and I didn't..

Conversion failed when converting from a character string to uniqueidentifier in SQL SERVER select statement

Error is pretty clear, that value isn't a uniqueidentifier.

If you check against a proper GUID (i.e. SELECT NEWID() -> 919053E6-7CE6-4324-9A58-A2EACA5E0F5F) notice that the first "block" has 8 characters (919053E6). Yours, however, has 7 characters (1822CBE).

On consideration, this is probably simply a typographical error.

Conversion failed when converting from string to uniqueidentifier - loading DataTable with ExecuteReader

You should always parameterise your SQL queries to help prevent SQL injection and avoid problems like you're facing now. See Why do we always prefer using parameters in SQL statements?.

Use SqlParameter to add the parameters to the SqlCommand.

string invoiceStatusSQL = @"select status, invoice_id from invoices where acct_id = @accountId";
command = new SqlCommand(invoiceStatusSQL, cnn);

SqlParameter idParam = new SqlParameter("@accountId", accountid);
command.Parameters.Add(idParam);

da.Load(command.ExecuteReader());

---

You can also specify the actual database type when creating the parameter, which will reduce any issues you might have with the framework inferring the type incorrectly (although I don't think that would happen in your case for a Guid/UniqueIdentifier). One way to specify the type is shown below.

var p = new SqlParameter 
            { 
                ParameterName = "@accountId", 
                SqlDbType = SqlDbType.UniqueIdentifier,  
                Value = accountid  
            };

Conversion failed error is showing for uniqueidentifier in SQL query

According to Microsoft documentation:

The uniqueidentifier type is considered a character type for the
purposes of conversion from a character expression, and therefore is
subject to the truncation rules for converting to a character type.
That is, when character expressions are converted to a character data
type of a different size, values that are too long for the new data
type are truncated. See the Examples section.

That explains why it works fine if you append characters after the 36th position.

When you prepend characters to the guid, you are breaking the formatting rules for the guid and then the conversion fails.

In a stored procedure you can validate the guid by using TRY_CONVERT. It will return NULL if the conversion is not possible:

IF TRY_CONVERT(UNIQUEIDENTIFIER,@userId) IS NULL
   BEGIN
      .... report error ...
   END

TRY_CONVERT is only available from SQL Server 2012. If you need to validate a string before conversion to UNIQUEIDENTIFIER on older versions, you can use the following code:

IF NOT @userId LIKE REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]')+'%'
    BEGIN
          .... report error ...
    END

How to convert charater string to uniqueidentifier and use Stuff function?

Please check if this fit your need:

declare @FromDate CHAR(10) ,@ToDate CHAR(10), @Item_List nvarchar(MAX)
select 
    @FromDate  = '2021-01-01',-- make sure to convert the DATE from the table to NVARCHAR using style 120
    @ToDate    = '2021-03-31',-- make sure to convert the DATE from the table to NVARCHAR using style 120
    @Item_List = '''a70014a3-2e00-41f0-9c3e-6fb8c4f2ab60'',''26dd67c1-fe37-41fa-b8c5-ff033928a291'''

------------------ Solution --------------------
-- Important! No reason for scalar function! Use it inline your query directly
SELECT 
    N'From Date : ' + @FromDate + ' to ' + @ToDate + N' Item No: IN (' + STRING_AGG(''''+ItemName+'''',',') + N')' 
FROM Item
WHERE ItemGuid in (
    -- Your string includes quotes which we must clear befor CONVERT to GUIDE
    SELECT REPLACE([value],'''','') FROM STRING_SPLIT(@Item_List, ',')
)

Note! concat input text to string might be NOT safe and NOT recommended. It has a potential to SQL Injection!

Note: You probably plan a dynamic query, which will SELECT data from the table using the above string, which you asked to build. In this case, this seems like "XY problem" since you probably do not need to build this string at all. You can use the same approach to split string input and execute a direct SELECT query from your table. If we knew what is your final requested result is, then we could help in this as well, but the approach is the same is above code

Conversion failed when converting from a character string to uniqueidentifier

this fails:

 DECLARE @vPortalUID NVARCHAR(32)
 SET @vPortalUID='2A66057D-F4E5-4E2B-B2F1-38C51A96D385'
 DECLARE @nPortalUID AS UNIQUEIDENTIFIER
 SET @nPortalUID = CAST(@vPortalUID AS uniqueidentifier)
 PRINT @nPortalUID

this works

 DECLARE @vPortalUID NVARCHAR(36)
 SET @vPortalUID='2A66057D-F4E5-4E2B-B2F1-38C51A96D385'
 DECLARE @nPortalUID AS UNIQUEIDENTIFIER
 SET @nPortalUID = CAST(@vPortalUID AS UNIQUEIDENTIFIER)
 PRINT @nPortalUID

the difference is NVARCHAR(36), your input parameter is too small!

---

Related Topics