Ошибка конверсии при преобразовании из символьной строки в ошибку uniqueidentifier в SQL Server

Я получаю сообщение об ошибке "Ошибка конверсии при преобразовании из символьной строки в uniqueidentifier" и, наконец, в конце моей веревки. Я сузил свою проблему как можно меньше, сохраняя ошибку в такте. Сначала установите разделитель CSV, если вы хотите воспроизвести:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Вот тестовый код. Я на SQL 2008R2, но в базе данных, совместимой с SQL 2005:

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, ',')
)

ЗДЕСЬ источник для функции DelimitedSplit8K, поэтому вам не нужно ее искать и находить:

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
;

Ответ 1

Не уверен, что здесь происходит, но проблема не в том, что формат гидов или выход функции. Выполнение этого действия:

declare @temp varchar(max) = '918E809E-EA7A-44B5-B230-776C42594D91,6F8DBB54-5159-4C22-9B0A-7842464360A5'    
select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')

Может быть, процессор запросов смотрит на схему возврата функции и говорит, что ее нельзя отнести к uniqueidentifier? Надеюсь, кто-то другой может дать конкретный ответ на этот вопрос.

Выбор выхода функции split в таблицу temp будет работать:

select cast(Item as uniqueidentifier) as Item into #temp from dbo.DelimitedSplit8K(@temp, ',')

-- 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, ',')
    select Item from #temp
)

Ответ 2

Использование этого UDF действительно делает процедурные предположения о порядке выполнения. Он предполагает, что предложение WHERE внутри UDF будет оцениваться до cast(item as uniqueidentifier). Это предположение ошибочно, так как оптимизатор может свободно изменять план, чтобы переместить предложение WHERE над литой, а сетевой эффект - преобразовать частичный токен в guid (т.е. Строку типа 18E809E-EA7A-44B5-B230-776C42594D91).

Для более подробного ответа читайте Функции T-SQL не подразумевают определенный порядок выполнения.

В качестве обходного пути вы можете заставить NULL в проецируемые значения UDF для строк, которые не соответствуют предложению WHERE:

CREATE FUNCTION dbo.DelimitedSplit8K
...
cteStart(N1, nullify) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1, 
                    case when (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) then 1 else 0 end
                   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       = case s.nullify
            when 1 then SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
            else null
            end
   FROM cteStart s;
go

Поскольку выражение CASE гарантированно оценивается до CAST (поскольку вход CAST является результатом CASE), переупорядочение предложения WHERE безопасно.

Ответ 3

Зачем бросать Item в uniqueidentifier, когда вы можете сделать это наоборот.

Вместо

where Col1 not in 
(
-- ERROR OCCURS HERE
    select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
)

вы можете попробовать следующее:

where cast(Col1 as varchar(64)) not in 
(
    select Item 
    from dbo.DelimitedSplit8K(@temp, ',')
)

Ответ 4

Похоже, я неправильно понял вопрос в первый раз. Хорошая работа, создающая тест script, который воспроизводит ошибку. Для меня работает следующее:

delete dbo.ZZZTESTTABLE
WHERE Col1 in
(
    select Z.Col1
    from dbo.ZZZTESTTABLE Z
    LEFT JOIN dbo.DelimitedSplit8K(@temp, ',') S on S.Item = Z.Col1
    where S.Item is null
)
OPTION (force order)