Function to pass an array : Table Variable « Transact SQL « SQL Server / T-SQL

1. SQL Server / T-SQL
2. Transact SQL
3. Table Variable

Function to pass an array

4> CREATE FUNCTION dbo.udf_ParseArray( @StringArray varchar(max),@Delimiter char(1) ,@MinRowSelect int,@MaxRowSelect int)
5> RETURNS @StringArrayTable TABLE (RowNum int IDENTITY(1,1), Val varchar(50))
6> AS
7> BEGIN
8>     DECLARE @Delimiter_position int
9>     IF RIGHT(@StringArray,1) != @Delimiter
10>         SET @StringArray = @StringArray + @Delimiter
11>         WHILE CHARINDEX(@Delimiter, @StringArray) <> 0
12>         BEGIN
13>             SELECT @Delimiter_position = CHARINDEX(@Delimiter, @StringArray)
14>             INSERT @StringArrayTable VALUES (left(@StringArray, @Delimiter_position - 1))
15>             SELECT @StringArray = stuff(@StringArray, 1, @Delimiter_position, '')
16>         END
17>         DELETE @StringArrayTable
18>         WHERE RowNum < @MinRowSelect OR RowNum > @MaxRowSelect
19>     RETURN
20> END
21> GO
1>
2> SELECT RowNum, Val
3> FROM udf_ParseArray('A,B,C,D,E,F,G', ',', 3, 5)
4> GO
RowNum      Val
----------- --------------------------------------------------
          3 C
          4 D
          5 E
1>
2> drop function dbo.udf_ParseArray
3> GO
1>

 

Related examples in the same category