Recursive Stored Procedures

Here is a short example of a recursive stored procedure.  It returns the nth piece of a delimited text string using recursion.  There are better performing methods for doing this type of task, however, I found it an interesting exercise none the less.


-------------------------------------------------------------------------------
-- Date:   October 12, 1999
-- Author: Keith R. Davis
-- Desc:   Returns the nth delimted piece of a text string.
--
-- params: @string     - delimited text string to parse
--         @delimiter  - text string delimiter character
--         @piece      - the nth delimited piece of the text string to return
--         @rtext  out - nth delimited piece of the text string
-------------------------------------------------------------------------------

CREATE PROCEDURE dbts_piece (
       @string varchar(8000),
       @delimiter char(1),
       @piece int,
       @rtext varchar(8000) OUTPUT
) AS

IF CHARINDEX(@delimiter, @string, 1) = 0
BEGIN
	IF @piece = 1
		SET @rtext = @string
	ELSE
		SET @rtext = ''
	RETURN
END

IF CAST(@string AS varchar) = @delimiter
BEGIN
	SET @rtext = ''
	RETURN
END

IF @piece = 1
BEGIN
	SET @rtext = SUBSTRING(@string, 1, CHARINDEX(@delimiter, @string, 1) - 1)
	RETURN
END

WHILE @piece > 1
BEGIN
	IF CHARINDEX(@delimiter, @string, 1) = 0
	BEGIN
		SET @rtext = ''
		RETURN
	END

  	SET @string = SUBSTRING(@string,  CHARINDEX(@delimiter, @string, 1) + 1, LEN(@string) - CHARINDEX(@delimiter, @string, 1))
	SET @piece = @piece - 1	

	EXEC dbts_piece @string, @delimiter, 1, @rtext OUTPUT
END

RETURN
Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *