Friday, June 12, 2009

SQL function to split a multivalue parameter


       In reports with multivalue parameters, the input parameter can be passed as a list to the sql server procedure where we can use a UDF to split it.
For ex if we a have a procedure to return employee names for multiple employee ids then the proc will look something like this

CREATE PROC [dbo].[proc_EmpName_getAll]
@EmpIDKey VARCHAR(1000)
AS
SELECT   EmpIDKey, EmpName FROM dbEmployee
      WHERE EmpIDKey IN (SELECT Item FROM  dbo.Split(@EmpIDKey, ',') AS Split_1)
ORDER BY EmpName

  The  sql function below takes a user defined multi dimensional object and a delimitter character as input parameters and breaks them into individual items. 

CREATE FUNCTION [dbo].[Split]/* This function is used to split up multi-value parameters */(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item NVARCHAR(50) collate database_default )
AS
BEGIN


     DECLARE
@tempItemList NVARCHAR(4000)
     SET @tempItemList = @ItemList

     DECLARE @i INT
     DECLARE @Item NVARCHAR(4000)

     SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
     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
   RETURN
END