ConvertIds2Table

ConvertIds2Table.sql

-- =============================================
-- Author:      Weijie JIN
-- Create date: 20120719
-- Description: Get list by Ids string
-- =============================================
ALTER PROCEDURE [dbo].[ConvertIds2Table]
    @Ids NVARCHAR(2000)
AS
BEGIN
    SET ARITHIGNORE ON 
    SET ARITHABORT OFF 
    SET ANSI_WARNINGS OFF 
    SET NOCOUNT ON 

    DECLARE @IdsPosition INT 
    DECLARE @ArrValue NVARCHAR(2000) 
    --must declare correct data type for IDs 
    DECLARE @TableVar TABLE ( Id NVARCHAR(50) NOT NULL ) 

    SET @Ids = COALESCE(@Ids ,'') 
    IF @Ids <> '' 
    BEGIN 
        --add comma to end of list so user doesn't have to 
        SET @Ids = @Ids + ',' 
        -- Loop through the comma delimited string list 
        WHILE PATINDEX('%,%' , @Ids ) <> 0 
        BEGIN 
            SELECT @IdsPosition = PATINDEX('%,%' , @Ids) 
            SELECT @ArrValue = left(@Ids, @IdsPosition - 1) 
            -- Insert parsed ID into TableVar for "where in select"
        INSERT INTO @TableVar (Id) VALUES (LTRIM(RTRIM(@ArrValue))) 
        -- Remove processed string 
        select @Ids = STUFF(@Ids, 1, @IdsPosition, '') 
    END 
    
    SELECT * FROM @TableVar
END

END