Table-valued function FTParsedINT

,

Usage Example:

 

IF @createScript = 'Y' AND (@listOfTestId = '' OR @currentId IN (SELECT * FROM dbo.FTParsedINT(@listOfTestId)))
BEGIN

-- script all test case if @listOfTestId is '' or script only the test in the list '1,2' etc.

.

.

.

END

 

/****** Object: UserDefinedFunction [dbo].[FTParsedINT] Script Date: 10/04/2007 10:24:16 ******/
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Mark Drobnis
-- Create date: 10/3/2007
-- Description:    parse comma seperated list into 
-- a table to use in a in clause
-- =============================================
ALTER FUNCTION [dbo].[FTParsedINT] 
(
    -- Add the parameters for the function here
    @commaSepList varchar(4000) 
     
)
RETURNS 
@TableList TABLE 
(
    -- Add the column definitions for the TABLE variable here
    Id int 
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    DECLARE @id AS INT
    DECLARE @commaFound INT
    
    WHILE @commaSepList <> ''
    BEGIN
        SET @commaFound = CHARINDEX(',',@commaSepList,0)
        IF @commaFound = 0
        BEGIN
            SET @id = @commaSepList
            SET @commaSepList = ''
            
            INSERT @TableList
            SELECT @id
        END
        ELSE
        BEGIN
            SET @id = Left(@commaSepList,@commaFound - 1)
            SET @commaSepList = SUBSTRING(@commaSepList,@commaFound + 1,LEN(@commaSepList) - @commaFound)

            INSERT @TableList
            SELECT @id

        END
    END
        
    RETURN 
END

Rate

3 (1)

Share

Share

Rate

3 (1)