Passing a List<string> to a stored procedure

Question

I have a string list and i need to check if any of the values in the list contains in the database table.if exists return the data set of existing values.

public DataSet CheckDocumentNumber(List<string> DocNumber)
{
   DataSet DocNum = new DataSet();
   SqlTransaction transaction = DALDBConnection.SqlConnection.BeginTransaction();

   try
   {
      string[] taleNames = new string[1];
      taleNames[0] = "DocNum";
      SqlParameter[] param = new SqlParameter[1];
      param[0] = new SqlParameter("@DocNumber", DocNumber);

      SqlHelper.FillDataset(transaction, CommandType.StoredProcedure, "spCheckDocNumber", DocNum, taleNames, param);
      transaction.Commit();
   }
   catch (Exception e)
   {
      transaction.Rollback();
   }

   return DocNum;
}

My stored procedure is

CREATE PROCEDURE spCheckDocNumber
    @DocNumber VARCHAR(MAX)
AS
BEGIN
   SELECT * FROM tblDocumentHeader WHERE DocumentNumber = @DocNumber
END

I need to know that how do I have to pass the list to the stored procedure and how to check the list with in the procedure. plz help

Answer 1

Crate a Split function that splits a string based on a char.

GO
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH splitter_cte AS (
      SELECT CHARINDEX(@sep, @s) as pos, 0 as lastPos
      UNION ALL
      SELECT CHARINDEX(@sep, @s, pos + 1), pos
      FROM splitter_cte
      WHERE pos > 0
    )
    SELECT SUBSTRING(@s, lastPos + 1,
                     case when pos = 0 then 80000
                     else pos - lastPos -1 end) as chunk
    FROM splitter_cte
  )
GO

SELECT *
  FROM dbo.Split(' ', 'the quick brown dog jumped over the lazy fox')
OPTION(MAXRECURSION 0);

Then use the Split function to break on a comma, then you can use the output as table that then joins against the table that you are looking for.

This can make splitting a comma separated list very easy. Then you can just pass in a string with all hte values seperated by a comma.

Hope this helps!