T-sql Function help

I have right following function to split , (Commas) when passing multiple values from stored procedures
But its look like not working
any help appriciate.
CREATE FUNCTION FnSplit
(
@List nvarchar(MAX),@SplitOn navarchar(5))
RETURNS @RtnValue table
(id int identity(1,1) Value navarchar(100))
AS
BEGIN
While (CharIndex(@SplitOn,@List)>0
BEGIN
INSERT INTO @RtnValue (value)
SELECT Value=ltrim(rtrim(Substring(@List,1,CharIndex(@SplitOn,@List)-1)))
SET @List=Substring(@List,CharIndex(@SplitOn,@List)+len(@SplitOn),len(@SplitOn))
END
INSERT INTO @RtnValue(Value)
SELECT Value=ltrim(rtirm(@List))
Return


END

Open in new window


stored procedure code is (part)
[Finance Year] IN (SELECT Value FROM dbo.fnSplit(@Fyear,','))
LVL 10
ukerandiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
CREATE FUNCTION FnSplit
(
@List nvarchar(MAX),@SplitOn nvarchar(5))
RETURNS @RtnValue table
(id int identity(1,1), Value nvarchar(100))
AS
BEGIN
While (CharIndex(@SplitOn,@List)>0
BEGIN
INSERT INTO @RtnValue (value) Values (ltrim(rtrim(Substring(@List,1,CharIndex(@SplitOn,@List)-1))));
SELECT @List=Substring(@List,CharIndex(@SplitOn,@List)+len(@SplitOn),len(@SplitOn))
END

INSERT INTO @RtnValue(Value) Values (rtrim(ltrim(@List)))
Return

END

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
It would really help if you could provide some more details behind 'not working'.  Experts here do not have access to your data source and can't run queries, so no idea what that means.
Vikas GargAssociate Principal EngineerCommented:
Hello,

Please find the new script for splitting functionality

ALTER FUNCTION [dbo].[FN_Split]
(
  @sInputList VARCHAR(8000),
  @sDelimiter CHAR(1) = ','
) RETURNS @List TABLE (row [int] identity(0,1) not null, item VARCHAR(8000))
/**
select * from FN_Split('hello,world,this,is,a,test',',')
****/
BEGIN

DECLARE @sItem VARCHAR(8000)

SET @sItem = ''

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
    SET @sItem= RTRIM(LTRIM(SUBSTRING(@sInputList,1, CHARINDEX(@sDelimiter, @sInputList, 0) - 1)))
    SET @sInputList= RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList, 0)+LEN(@sDelimiter),LEN(@sInputList))))
    INSERT INTO @List SELECT @sItem
END

INSERT INTO @List SELECT CASE WHEN LEN(@sInputList) < 1 THEN NULL ELSE @sInputList END

RETURN


END 

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
There are many split function available

This one is cited often as being very efficient (e.g. here) and there many many others
CREATE FUNCTION [dbo].[SplitStringIntoTable]  (
    @string varchar(8000),
    @delimiter varchar(5)
    )
  /*
    usage samples
    
      select *
      from dbo.SplitStringIntoTable('2011,2012,1013,2014,2015', ',')
    
      SELECT * FROM (SELECT 'ab/c/def/ghijklm/no/prq/////st/u//' AS string) AS test_values 
      CROSS APPLY DBA.dbo.SplitStringIntoTable(test_values.string, '/')
  */
RETURNS TABLE WITH SCHEMABINDING 
AS
RETURN
--Inline-CTE-Driven "tally table" produces values from 0 to 9999  ... enough to cover varchar(8000)
WITH cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s] --0 thru 9
    CROSS JOIN cteDigits [10s] --thru 99
    CROSS JOIN cteDigits [100s] --thru 999
    CROSS JOIN cteDigits [1000s] --thru 9999
),
cteBase(num) AS ( --Limit the number of rows up front, for both a performance gain and prevention of accidental "overruns"
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@string, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM cteTally
), 
cteStart(num1) AS ( --This returns num + 1 (starting position of each "value" just once for each delimiter)
        SELECT b.num + 1
        FROM cteBase b
        WHERE (SUBSTRING(@string, b.num, 1) = @delimiter OR b.num = 0)
)
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. 
SELECT ROW_NUMBER() OVER(ORDER BY s.num1) AS value_seq,
       SUBSTRING(@string, s.num1, ISNULL(NULLIF(CHARINDEX(@delimiter, @string, s.num1), 0) - s.num1, 8000)) AS value
FROM cteStart s;
GO

Open in new window

also see this article:  Delimited list as parameter: what are the options?
ukerandiAuthor Commented:
Hi Jim ,
Afetr run script below, the results is
showing
DECLARE @Test nvarchar(MAX)
SET @Test='2005,2006,2007'
SELECT  Value  FROM  dbo.FnSplit(@Test,',')

RESULTS:
2005
2

This is I used for the MS report builder , when i used 2015 and 2014 (Multiple parameters) , i will get only 2015 results only
Vitor MontalvãoMSSQL Senior EngineerCommented:
The way you're using the SUBSTRING is wrong. The correct way should be:
SELECT @List=Substring(@List,CharIndex(@SplitOn,@List)+1,len(@List)-CharIndex(@SplitOn,@List))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.