3V5pt6Sc

1SET XACT_ABORT ON; BEGIN TRAN;
2IF OBJECT_ID('[dbo].[udf_PivotParameters]','TF') IS NOT NULL DROP FUNCTION [dbo].[udf_PivotParameters];
3GO
4CREATE FUNCTION [dbo].[udf_PivotParameters]
5    (
6      @ParamaterList VARCHAR(MAX),
7      @Delimiter CHAR(1)
8    )
9RETURNS @ReturnList TABLE
10    (
11      FieldValue VARCHAR(MAX)
12    )
13AS BEGIN
14    DECLARE @ArrayList TABLE
15        (
16          FieldValue VARCHAR(MAX)
17        )
18    DECLARE @Value VARCHAR(MAX)
19    DECLARE @CurrentPosition INT
20 
21    SET @ParamaterList = LTRIM(RTRIM(@ParamaterList))
22        + CASE WHEN RIGHT(@ParamaterList, 1) = @Delimiter THEN ''
23               ELSE @Delimiter
24          END
25    SET @CurrentPosition = ISNULL(CHARINDEX(@Delimiter, @ParamaterList, 1), 0)
26    IF @CurrentPosition = 0
27        INSERT  INTO @ArrayList ( FieldValue )
28                SELECT  @ParamaterList
29    ELSE
30        BEGIN
31            WHILE @CurrentPosition > 0
32                BEGIN
33                    SET @Value = LTRIM(RTRIM(LEFT(@ParamaterList, @CurrentPosition - 1))) --make sure a value exists between the delimiters
34                    IF LEN(@ParamaterList) > 0
35                        AND @CurrentPosition <= LEN(@ParamaterList)
36                        BEGIN
37                            INSERT  INTO @ArrayList ( FieldValue )
38                                    SELECT  @Value
39                        END
40                    SET @ParamaterList = SUBSTRING(@ParamaterList,
41                                                   @CurrentPosition
42                                                   + LEN(@Delimiter),
43                                                   LEN(@ParamaterList))
44                    SET @CurrentPosition = CHARINDEX(@Delimiter,
45                                                     @ParamaterList, 1)
46                END
47        END
48    INSERT  @ReturnList ( FieldValue )
49            SELECT  FieldValue
50            FROM    @ArrayList
51    RETURN
52   END
53GO
54IF @@ERROR<>0 ROLLBACK ELSE WHILE @@TRANCOUNT>0 COMMIT;
55GO
56-- Permissions