I am getting this error when executed below query:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_Split", or the name is ambiguous.
How do I use the split function on my where clause? The split function name is: dbo.splitfuntion
Here is my query:
Declare @Column2value varchar(max)
Set @Column2value = 'ABC,CDE,FGH,IJK'
Select column1, column2, column3, column4
From MediumTable
Where column2 = dbo.splitfuntion (@Column2value,',')
When funtion being call in the where clause, is that the best efficient way?
I strongly suspect that it is a table-valued function, not a scalar function, so you need to do something like this, including changing the column name to what is actually returned by the function, of course:
Select mt.column1, mt.column2, mt.column3, mt.column4
From MediumTable mt
Where Exists(Select 1 From dbo.splitfuntion (@Column2value,',') sf Where sf.column_name = mt.column2)
Assuming your function is a table valued function, do something like this:
DECLARE @Column2value VARCHAR(MAX);
SET @Column2value = 'ABC,CDE,FGH,IJK';
SELECT column1 ,
column2 ,
column3 ,
column4
FROM MediumTable m
INNER JOIN dbo.splitfuntion(@Column2value, ',') s ON
s.OutputColumnNameFromSplitFunction = m.column2;
On second thoughts, what @ScottPletcher suggested might be better because if you have dups in the @Column2value, if you use my method, you will get duplicate output.