我得到错误“从字符串转换为唯一标识符时转换失败”,最后我的绳索结束.我把我的问题缩小到尽可能小的同时保持错误的机智.如果要复制,请先安装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
;
解决方法
不知道这里发生了什么,但问题似乎不是guid的格式或函数的输出.执行此作品：
declare @temp varchar(max) = '918E809E-EA7A-44B5-B230-776C42594D91,6F8DBB54-5159-4C22-9B0A-7842464360A5' select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp,')
也许查询处理器正在查看函数的返回模式,并说它不能被转换为uniqueidentifier？希望别人可以提供具体的答案.
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
)
