ServiceManager.Report.Common.Function.fn_CSVToTableInt.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.Common.Function.fn_CSVToTableInt.Install" Accessibility="Public" FileName="ServiceManager.Report.Common.Function.fn_CSVToTableInt.Install.sql"/>

File Content: ServiceManager.Report.Common.Function.fn_CSVToTableInt.Install.sql

SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO





-- Drop function if it already exists


IF EXISTS (


  SELECT * 


    FROM sys.all_objects


   WHERE type = N'TF'


     AND name = N'fn_CSVToTableInt' 


)


   DROP FUNCTION dbo.fn_CSVToTableInt


GO





CREATE FUNCTION dbo.fn_CSVToTableInt (@stringarray nvarchar(max))


RETURNS @table TABLE


(


	value int


)


AS


BEGIN


begin


  declare @ptr int, @nextptr int, @length int





  select @ptr = 0,  @nextptr = 1





 while @nextptr > 0


    begin


    select @nextptr = charindex(',', @stringarray, @ptr + 1)


    select @length = case when @nextptr > 0


          then @nextptr


          else len(@stringarray) + 1


        end - @ptr - 1


    insert @table (value)


    values (convert(int, substring(@stringarray, @ptr + 1, @length)))


    select @ptr = @nextptr


    end





   return 


end


END


GO