title: Conversion failed when converting from a character string to uniqueidentifier error in SQL Server
body: <p>I've been getting the error "Conversion failed when converting from a character string to uniqueidentifier" and am finally at the end of my rope. I've narrowed down my problem to as small as possible while keeping the error in tact. Install the CSV splitter from here first if you want to reproduce: </p>
  
  <p><a href="http://www.sqlservercentral.com/articles/Tally+Table/72993/" rel="noreferrer">http://www.sqlservercentral.com/articles/Tally+Table/72993/</a></p>
  
  <p>Here's the test code. I'm on SQL 2008R2 but in a database that is SQL 2005 compatible:</p>
  
  <pre><code>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZZZTESTTABLE]') AND type in (N'U'))
  DROP TABLE [dbo].[ZZZTESTTABLE]
  GO
  
  CREATE TABLE [dbo].[ZZZTESTTABLE](
      [Col1] [uniqueidentifier] NOT NULL,
   CONSTRAINT [PK_ZZZTESTTABLE] PRIMARY KEY CLUSTERED 
  (
      [Col1] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  -- Test table that I would like to check my values against
  insert dbo.ZZZTESTTABLE(Col1) values('85B049B7-CDD0-4995-B582-5A74523039C0')
  
  -- Test string that will be split into table in the DelimitedSplit8k function
  declare @temp varchar(max) = '918E809E-EA7A-44B5-B230-776C42594D91,6F8DBB54-5159-4C22-9B0A-7842464360A5'
  
  -- I'm trying to delete all data in the ZZZTESTTABLE that is not in my string but I get the error 
  delete dbo.ZZZTESTTABLE
  where Col1 not in 
  (
  -- ERROR OCCURS HERE
      select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
  )
  </code></pre>
  
  <p>HERE's the source for the DelimitedSplit8K function so you don't have to go and find it:</p>
  
  <pre><code>CREATE FUNCTION dbo.DelimitedSplit8K
  --===== Define I/O parameters
          (@pString VARCHAR(8000), @pDelimiter CHAR(1))
  RETURNS TABLE WITH SCHEMABINDING AS
   RETURN
  --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
       -- enough to cover VARCHAR(8000)
    WITH E1(N) AS (
                   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                  ),                          --10E+1 or 10 rows
         E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
         E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
   cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                       -- for both a performance gain and prevention of accidental "overruns"
                   SELECT 0 UNION ALL
                   SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                  ),
  cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                   SELECT t.N+1
                     FROM cteTally t
                    WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
                  )
  --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
   SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
          Item       = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
     FROM cteStart s
  ;
  </code></pre>