Cannot find either column “dbo” or the user-defined function or aggregate “dbo.FN_Split”, or the name is ambiguous

Question

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

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)

Please help to get out of it...

Select the result of `dbo.FN_Split(@p_SourceText, @p_Delimeter)` into a text variable and use that for your `@sql` string building. — Allan S. Hansen, Dec 18 '13 at 07:04
Function dbo.FN_Split(@p_SourceText, @p_Delimeter) returns a TABLE, not a single variable, so the usage would be SELECT * FROM dbo.FN_Split(@p_SourceText, @p_Delimeter) and not SELECT dbo.FN_Split(@p_SourceText, @p_Delimeter) — Adriaan Stander, Dec 18 '13 at 07:09
The bug in the question *title* is a simple typo, but since `FN_Slit` doesn't appear anywhere else in the question, I'll just assume it's a transcription error and not the actual problem. — Damien_The_Unbeliever, Dec 18 '13 at 07:24
oh no I did type mistake function name is "FN_split" instead of "FN_slit" — Shashikant Mitkari, Dec 18 '13 at 12:02

score 2 · Accepted Answer · answered Dec 18 '13 at 07:16

First, make sure you did run the create script in the correct database.

Second, as @astander started mentioning, you are using the function results incorrectly.

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

would become:

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.

you must use `select` instead of `values` when inserting, since you are potentially inserting more than one value: `Insert into person select items from dbo.FN_Split('111|Mitchell|Johnson,|')` where orderId = 0` Note you also need quotes around your csv item. — ps2goat, Dec 18 '13 at 15:58

score 0 · Answer 2 · answered Dec 18 '13 at 07:16

0

I think to clarify the comment from astander

try your code like this :

while @i<=@max
begin
if @i=0
begin
set @sql='insert into Person select items from dbo.FN_Split(@p_SourceText,  
 @p_Delimeter) where orderId = 00'
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

answered Dec 18 '13 at 07:16

Mathese F

559
4
9

parameters need to be concatenated dynamically; they shouldn't be literals. – ps2goat Dec 18 '13 at 07:17
oh yes but you can specify them with your exec or like described here upper. exec usp_spexecutesql @sql, N'@p_sourceText varchar(30)', p_sourcetext = 'value' – Mathese F Dec 18 '13 at 07:23

Shantu · Answer 3 · 2017-07-13T16:25:10.540

0

For my case it was really a function missing when i exported data from one database to another. Can not be found in the destination database > Functions > Scalar-valued Functions. Then i created [dbo].[equals] and it worked. (copied from source database)

edited Jul 13 '17 at 16:25

answered Jul 13 '17 at 16:19

Shantu

145
11

Cannot find either column “dbo” or the user-defined function or aggregate “dbo.FN_Split”, or the name is ambiguous

3 Answers3

Linked