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

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..

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.

You need to treat a table valued udf like a table, eg JOIN it

select Emp_Id 
from Employee E JOIN dbo.Splitfn(@Id,',') CSV ON E.Emp_Id = CSV.items 

A general answer

select * from [dbo].[SplitString]('1,2',',') -- Will work 

but

select [dbo].[SplitString]('1,2',',')  -- will **not** work and throws this error

Since people will be coming from Google, make sure you're in the right database.

Running SQL in the 'master' database will often return this error.

Database -> Tables -> Functions -> Scalar Valued Functions - dbo.funcName 
rightClick => Properties -> Search UserRoles + Add user access