SQL: Split Procedure

I decided to include a couple tools that I use quite frequently so that there is ease of use. I cannot take the credit and this is probably on many people's blogs but I wanted to centralize my most common ones. Thanks to those who contributed.

This Stored Procedure will allow for separating a comma delimited list and returning a table.

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   ALTER PROCEDURE [dbo].[Split] ( @ItemList NVARCHAR(4000), @delimiter CHAR(1) )   AS   BEGIN     DECLARE @IDTable TABLE (Item VARCHAR(100)) DECLARE @tempItemList NVARCHAR(4000) SET @tempItemList = @ItemList   DECLARE @i INT DECLARE @Item NVARCHAR(4000)   SET @tempItemList = REPLACE (@tempItemList, ' ', '') SET @i = CHARINDEX(@delimiter, @tempItemList)   WHILE (LEN(@tempItemList) > 0) BEGIN IF @i = 0 SET @Item = @tempItemList ELSE SET @Item = LEFT(@tempItemList, @i - 1) INSERT INTO @IDTable(Item) VALUES(@Item) IF @i = 0 SET @tempItemList = '' ELSE SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) SET @i = CHARINDEX(@delimiter, @tempItemList) END     SELECT Item FROM @IDTable END

 

The utilization would be something like:

CREATE TABLE #MgrList (ManagersUID VARCHAR(50))   INSERT INTO #MgrList EXEC Split '100,101,102,103', ','