UDF Split problem

• Sy-1148362

  Right there with Babe

  Points: 759

  More actions

  September 1, 2009 at 11:28 am

  #210391

  Hi Experts

  I have a report with a single parameter 'ID' where i should enter multiple values with ',' delimeter.

  I am using a user defined split function as below to achieve it.

  -- The Split function is as below:

  create function [dbo].[split] (@string nvarchar(4000), @delimiter char(1))

  returns @results table (items nvarchar(4000))

  as

  begin

  declare @index int

  declare @slice nvarchar(4000)

  select @index = 1

  if @string is null return

  while @index != 0

  begin

  select @index = charindex(@delimiter, @string)

  if @index != 0

  select @slice = left(@string, @index - 1)

  else

  select @slice = @string

  insert into @results(items) values (@slice)

  select @string = right(@string, len(@string) - @index)

  if len(@string) = 0 break

  end

  return

  end

  I am using this split function for the 'ID' parameter in the dataset as below:

  TableName.ID IN (Select Items from dbo.Split(@ID, ',')))

  In the preview tab, when I give 2 or less values to the parameter, the code works fine.

  When ever I provide more than 2 values into the parameter it gives me result for only first 2 parameter values

  Eg: ID: 100,200,300,400

  The result is given only for the first 2 values 100 and 200. It does not give result for 300 and 400. When I check report individually for parameter values 300 or 400, there is data for those values as well. I am not sure why the report gives results based on the first 2 values.

  Moreover, if i go for a sum on the records with 4 values (100, 200, 300, 400), the overall total is correct, but details are given only for first 2 values given as parameter values.

  Please help me in fixing this issue.

  Thanks in advance

  Sy

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 1, 2009 at 11:31 am

  #1047701

  Try this udf.

• Sy-1148362

  Right there with Babe

  Points: 759

  More actions

  September 1, 2009 at 12:22 pm

  #1047734

  Hi Lynn

  Thanks for your immediate response.

  I tried the new UDF "DelimitedSplit", but still facing the same problem. The report still gives result for the first 2 values given to the parameter and ignores the other.

  Any idea, why is my report considering only first 2 values given to the parameter. Any idea when it happens. Was anything wrong with the UDF i mentioned above in the post?

  Please Help

  Thanks in advance

  Sy

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 1, 2009 at 12:37 pm

  #1047745

  Need to see the code, and not for the split function, but where you are using it. The DDL for the tables and sample data would also help in identifying the issue.

  Please read the first article I reference in my signature block below to see how you should post that information.