others - 在 SQL Server 中,从字符串转换为uniqueidentifier错误时失败

我得到了错误 "Conversion failed when converting from a character string to uniqueidentifier",

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
;

时间:

执行此工作:


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

选择split函数的输出到一个临时表将起作用:


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
)

18E809E-EA7A-44B5-B230-776C42594D91


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

为什么要将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, ',')
)

以下工作适用于我:


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)

...