Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Splitfn", or the name is ambiguous

Question

Hai guys,

I ve used the following split function,

CREATE FUNCTION dbo.Splitfn(@String varchar(8000), @Delimiter char(1))        returns @temptable TABLE (items varchar(8000))         as        begin        declare @idx int        declare @slice varchar(8000)         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(Items) values(@slice)             set @String = right(@String,len(@String) - @idx)            if len(@String) = 0 break        end    return        end   

and i used this function in a query and it was executed

ALTER PROCEDURE [dbo].[Employees_Delete]  -- Add the parameters for the stored procedure here @Id varchar(50) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;  -- Insert statements for procedure here   if exists( select Emp_Id from Employee where Emp_Id=dbo.Splitfn(@Id,',')) begin     update Employee set Is_Deleted=1 where Emp_Id=dbo.Splitfn(@Id,',')     select 'deleted' as message end  END 

but when i excute my store procedure giving values say (1,2) i got the error

Cannot find either column "dbo" or the user-defined  function or aggregate "dbo.Splitfn", or the name is ambiguous. 

I ve checked my tablevalued functions the function 'splitfn' was there but i dont know what is going wrong? Any suggestions..

Answer 1

It's a table-valued function, but you're using it as a scalar function.

Try:

where Emp_Id IN (SELECT i.items FROM dbo.Splitfn(@Id,',') AS i) 

But... also consider changing your function into an inline TVF, as it'll perform better.