Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Splitfn", or the name is ambiguous
140,634
Solution 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.
Solution 2
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
Solution 3
A general answer
select * from [dbo].[SplitString]('1,2',',') -- Will work
but
select [dbo].[SplitString]('1,2',',') -- will **not** work and throws this error
Solution 4
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.
Author by
ACP
Updated on September 27, 2021Comments
-
ACP over 2 years
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..
-
ACP over 14 years@Rob it worked for me.. How to change it into an inline TVF.. Plz guide me
-
Rob Farley over 14 yearsThere are lots of examples around. Here's one: sqlserverpedia.com/blog/sql-server-bloggers/…
-
gsirianni almost 13 yearsThanks for the answer. I was having the same problem and I couldn't figure out for the life of me what was going on. You saved me a huge headache.
-
Ian Kemp over 7 yearsFor anyone who's looking for the site @RobFarley linked to: web.archive.org/web/20110319054025/http://sqlserverpedia.com/…
-
Tolu almost 6 yearsFor those struggling with replication errors. This should fix it!