mosbah: Passing COMMA SEPARATED LIST into an IN clause within a SQL function

Passing a string directly into the IN clause is not possible. However, if you are providing the list as a string to a stored procedure, for example, you can use the following dirty method.
First, create this function:

CREATE FUNCTION [dbo].[fnNTextToIntTable] (@Data NTEXT)
RETURNS 
    @IntTable TABLE ([Value] INT NULL)
AS
BEGIN
    DECLARE @Ptr int, @Length int, @v nchar, @vv nvarchar(10)

    SELECT @Length = (DATALENGTH(@Data) / 2) + 1, @Ptr = 1

    WHILE (@Ptr < @Length)
    BEGIN
        SET @v = SUBSTRING(@Data, @Ptr, 1)

        IF @v = ','
        BEGIN
            INSERT INTO @IntTable (Value) VALUES (CAST(@vv AS int))
            SET @vv = NULL
        END
        ELSE
        BEGIN
            SET @vv = ISNULL(@vv, '') + @v
        END

        SET @Ptr = @Ptr + 1
    END

    -- If the last number was not followed by a comma, add it to the result set
    IF @vv IS NOT NULL
        INSERT INTO @IntTable (Value) VALUES (CAST(@vv AS int))

    RETURN
END

(Note: this is not my original code, but thanks to versioning systems here at my place of work, I have lost the header comment linking to the source.)
Then use it like so:

SELECT  *
FROM    tblMyTable
        INNER JOIN fnNTextToIntTable(@MyList) AS List ON tblMyTable.ID = List.Value

EXAMPLE FOR STORED USING FUNCTION

CREATE PROCEDURE [dbo].[USERS_SEARCH]
@USER_ID INT = NULL,
@USER_NAME NVARCHAR(50)=NULL,
@USER_FULL_NAME NVARCHAR(500) = NULL,
@DEPARTMENT_ID INT = NULL ,
@SECTION_ID INT = NULL ,
@DIVISION_ID INT = NULL ,
@CLINIC_ID INT = NULL ,
@JOB_TILE_ID VARCHAR(50) = NULL
AS
BEGIN
DECLARE @DOCTORS_JOB_TITLE_ID INT = 2;
DECLARE @ACTIVE_USER INT =1;
--IF POSITION IS DOCTORS SO WE WILL GET ALL EMPLOYEES WHO JOB ID IS DOCTOR ALSO
BEGIN
SELECT DISTINCT USER_ID,JOB_TITLE_ID,POSITION_ID, USER_FULL_NAME ,USER_NAME,
USER_FULL_NAME_EN , DEPARTMENT_ID,SECTION_ID,DIVISION_ID ,CLINIC_ID ,
DBO.DEPARTMENT_GETNAMEBYID(DEPARTMENT_ID) AS DEPARTMENT_NAME,
DBO.CLINIC_GETCLINIC_NAMEBYID(CLINIC_ID) AS CLINIC_NAME,
DBO.SECTION_GETNAMEBYID(SECTION_ID) AS SECTION_NAME,
DBO.DIVISION_GETNAMEBYID(DIVISION_ID) AS DIVISION_NAME
FROM [DBO].[USERS]
WHERE (@USER_ID IS NULL OR USER_ID =@USER_ID) AND
(@USER_FULL_NAME IS NULL OR USER_FULL_NAME_EN LIKE '%'+@USER_FULL_NAME+'%' OR USER_FULL_NAME LIKE '%'+@USER_FULL_NAME+'%') AND
( @DEPARTMENT_ID IS NULL OR DEPARTMENT_ID=@DEPARTMENT_ID)AND
( @SECTION_ID IS NULL OR SECTION_ID=@SECTION_ID)AND
( @DIVISION_ID IS NULL OR DIVISION_ID=@DIVISION_ID)AND
( @CLINIC_ID IS NULL OR CLINIC_ID=@CLINIC_ID)AND
(@JOB_TILE_ID IS NULL OR JOB_TITLE_ID IN (SELECT Value FROM COMMA_SEPARATED_LIST_TO_INT_TABLE(@JOB_TILE_ID)))
AND ( STATUS_ID=@ACTIVE_USER);
END;
END;

mosbah

Monday, April 14, 2014

Passing COMMA SEPARATED LIST into an IN clause within a SQL function

No comments:

Post a Comment