SQL IN clause with string parameter not listing all records

Question

SQL IN clause with string parameter not listing all records

I am passing string variable at IN position in sql (Stored Procedure). When declaring and setting a variable in sql, I get back all the data I need. But when setting a variable from C #, I only get data based on the first state inside that parameter.

I have a function to split statuses in a paramater list to get records:

    ALTER FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) 
    , @sDelimiter VARCHAR(10) = ';'
    ) RETURNS @List TABLE (item VARCHAR(8000))

    BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
      BEGIN
        SELECT
           @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
           @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

       IF LEN(@sItem) > 0
          INSERT INTO @List SELECT @sItem
       END

       IF LEN(@sInputList) > 0
          INSERT INTO @List SELECT @sInputList
          RETURN
       END

My stored procedure is structured like this:

    ALTER procedure [dbo].[Get_RequestsAtEachStage]
    (@managerRef int,
    @status varchar(20))

    as

    BEGIN

WITH MaxStatusDate
as
(
    select rs.requestID,rs.status from (
    SELECT requestID,MAX([DateCreated]) AS MaxDate
    FROM [LoanRequest].[dbo].[requestStatus]
    GROUP BY RequestID) maxg
    inner join [LoanRequest].[dbo].[requestStatus] rs on maxg.requestid = rs.requestid and maxg.MaxDate = rs.DateCreated
  )
    SELECT lr.ID, lr.serialNo, lr.model, lr.clientName, lr.address, lr.telephone, lr.contactName,  
    lr.swop, lr.substitueOfGoods, lr.printFunction, lr.copyFunction, lr.scanFunction, 
    lr.faxFunction, lr.controller, lr.controllerEmailAddress, 
    ml.Name, wl.Location, rt.requestType AS RequestTypeName, rs.status 
    FROM [dbo].[loanRequest] lr 
    INNER JOIN [dbo].[managersList] ml ON lr.managerRef = ml.ID 
    INNER JOIN [dbo].[warehouseList] wl ON lr.warehouseID = wl.ID 
    INNER JOIN [dbo].[requestType] rt ON lr.requestType = rt.ID 
    INNER JOIN MaxStatusDate rs ON lr.ID = rs.requestID 
    WHERE (@managerRef is null or lr.managerRef = @managerRef) AND rs.status IN (SELECT item FROM [dbo].[fnSplit](@status, ';'))

    END

Based on the page the user accesses, it will post through the relevant articles and retrieve the required entries.

Setting parameters in sql as follows works perfect, I am fetching all records:

   DECLARE @managerRef INT
   DECLARE @status NVARCHAR(100)

   SET @managerRef = NULL
   SET @status = 'Allocated;Readings Updated'

But when I post it via C # inside a string, it only fetches records with Allocated status .:

    string status = "Allocated;Readings Updated";
    DataTable dtDevices = d.PopulateDevicesApproval(managerRef, status);

My method to fetch data from sql:

     string filterstring = "";

            filterstring = "Get_RequestsAtEachStage ";

            cn = new SqlConnection(GetConnectionString());
            SqlCommand myCmd = new SqlCommand(filterstring, cn);
            myCmd.CommandType = CommandType.StoredProcedure;
            cn.Open();

            myCmd.Parameters.AddWithValue("@managerRef", managerRef);
            myCmd.Parameters.AddWithValue("@status", status);

            DataTable dt = new DataTable();
            dt.Load(myCmd.ExecuteReader());

            return dt;