69

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 don't know what is going wrong? Any suggestions..

Matthew Lock
  • 13,144
  • 12
  • 92
  • 130
ACP
  • 34,682
  • 100
  • 231
  • 371

5 Answers5

108

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.

Rob Farley
  • 15,625
  • 5
  • 44
  • 58
  • @Rob it worked for me.. How to change it into an inline TVF.. Plz guide me – ACP Jan 19 '10 at 07:54
  • There are lots of examples around. Here's one: http://sqlserverpedia.com/blog/sql-server-bloggers/splitting-a-delimited-string-part-1/ – Rob Farley Jan 19 '10 at 09:55
  • Thanks 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. – gsirianni Sep 20 '11 at 18:14
  • 7
    For anyone who's looking for the site @RobFarley linked to: http://web.archive.org/web/20110319054025/http://sqlserverpedia.com/blog/sql-server-bloggers/splitting-a-delimited-string-part-1/ – Ian Kemp Jan 19 '17 at 15:21
13

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
gbn
  • 422,506
  • 82
  • 585
  • 676
12

A general answer

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

but

select [dbo].[SplitString]('1,2',',')  -- will **not** work and throws this error
Arun Prasad E S
  • 9,489
  • 8
  • 74
  • 87
6

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.

PBeezy
  • 1,222
  • 2
  • 17
  • 26
0
Database -> Tables -> Functions -> Scalar Valued Functions - dbo.funcName 
rightClick => Properties -> Search UserRoles + Add user access

enter image description here

rohit.khurmi095
  • 2,203
  • 1
  • 14
  • 12