找不到列“dbo"或用户定义的函数或聚合“dbo.FN_Split",或者名称不明确 [英] Cannot find either column “dbo” or the user-defined function or aggregate “dbo.FN_Split”, or the name is ambiguous
问题描述
我有以下功能可以接受 CSV &分隔符分裂它
I have following function which accepts the CSV & delimeter & splits it
ALTER FUNCTION [dbo].[FN_Split] (@String varchar(max), @Delimiter char(1))
returns @temptable TABLE (orderId int,items varchar(max))
as
begin
declare @idx int
declare @i int=0
declare @slice varchar(8000)
declare @orderId int = 0 --<added a counter
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(orderId, Items) values(@orderId, @slice)
set @orderId = @orderId+1 --<increment the counter
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
&像这样使用上述函数的存储过程 &将结果插入数据库:
& the stored procedure like this which uses the above function & inserts the result into the database:
ALTER PROCEDURE dbo.StoredProcedure3
(
--@tableName nvarchar(max),
@p_SourceText nvarchar(max),
@p_Delimeter nvarchar(100)=','
)
AS
BEGIN
DECLARE @sql nvarchar(max)
--select * from fn_ParseText2Table(@p_SourceText, @p_Delimeter)
--insert into Person values (@sql)
declare @i int=0
DECLARE @max int=3
while @i<=@max
begin
if @i=0
begin
set @sql='insert into Person values( select items from'+dbo.FN_Split(@p_SourceText,
@p_Delimeter)+ 'as where orderId ='+0+')'
end
else
begin
if @i=(@max-1)
begin
set @sql=@sql+'UNION select items from'+ dbo.FN_Split(@p_SourceText,
@p_Delimeter)+' where orderId ='+@i+')'
end
else
begin
set @sql=@sql+'UNION select items from'+ dbo.FN_Split(@p_SourceText,
@p_Delimeter)+ 'where orderId ='+@i+') UNION'
end
end
set @i=@i+1
end
END
但在执行程序后,我收到以下错误:找不到列dbo"或用户定义的函数或聚合dbo.FN_Split",或者名称不明确.没有受影响的行.(返回 0 行)
But After Executing the Procedure I get The Following error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FN_Split", or the name is ambiguous. No rows affected. (0 row(s) returned)
请帮助摆脱它...
推荐答案
首先,确保您确实在正确的数据库中运行了创建脚本.
First, make sure you did run the create script in the correct database.
其次,正如@astander 开始提到的,您使用的函数结果不正确.
Second, as @astander started mentioning, you are using the function results incorrectly.
您的函数返回一个表,而不是一个值.您需要将函数作为 sql 语句的一部分执行,而不是在构建临时查询期间执行.例如,这段代码:
Your function returns a table, not a value. You'll need to execute the function as part of your sql statements, not during your build of ad hoc queries. For example, this code:
set @sql=@sql+'UNION select items from'+ dbo.FN_Split(@p_SourceText,
@p_Delimeter)+' where orderId ='+@i+')'
会变成:
set @sql = @sql+'UNION select items from dbo.FN_Split(' + @p_SourceText +', ' +
@p_Delimeter + ') where orderId =' + @i + ')'
在您当前引用该函数的任何地方进行类似的更改.
Make similar changes everywhere you are currently referencing the function.
这篇关于找不到列“dbo"或用户定义的函数或聚合“dbo.FN_Split",或者名称不明确的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!