Passing a List 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

How many records do you expect the String variable can have ? — Pankaj, Apr 09 '12 at 14:41
Do you have control over the stored procedure, i.e., are you allowed to change it? You would need to modify your stored procedure either to use table-valued parameters (http://mindlesspassenger.wordpress.com/2011/05/09/table-valued-parameters-codefirst-and-stored-procedures-3/), xml, or include the list into the query with dynamic sql. Or you will have to call the stored procedure once for every element in the list, which will be inefficient for large lists. — mellamokb, Apr 09 '12 at 14:42

score 6 · Accepted Answer · answered Apr 09 '12 at 15:40

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!

score 1 · Answer 2 · edited May 23 '17 at 11:58

You can use code like this: This works for SQL Server 2005 (and later):

create procedure IGetAListOfStrings
@List xml -- This will recevie a List of values
  as
begin
  -- You can load then in a temp table or use it as a subquery:
  create table #Values (ListValue nvarchar(20)); -- adjust nvarchar size
  INSERT INTO #Values
  SELECT DISTINCT params.p.value('.','varchar(20)') -- adjust nvarchar size
  FROM @List.nodes('/params/p') as params(p);
  ...
end

You have to invoke this procedure with a parameter like this:

exec IGetAListOfValues
@List = '<params> <p>string1</p> <p>string2</p> </params>' -- xml parameter

The nodes function uses an xPath expression. In this case, it's /params/p so that the XML uses <params> as root, and <p> as element.

For more information, see this answer: Passing List of values to stored procedure

score 0 · Answer 3 · answered Apr 09 '12 at 14:42

0

Maybe you could use the In operator in sql instead. There are a few tutorials on how to use this on http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm as well.

answered Apr 09 '12 at 14:42

Patrick

17,669
6
70
85

How can i operate IN predicate on Comma Seperated String Value in Database? – Pankaj Apr 09 '12 at 14:44
You would need to use dynamic sql to accomplish this, but it is definitely possible. – mellamokb Apr 09 '12 at 14:44
1

@Mellamokb OP need to pass the collection to database first. – Pankaj Apr 09 '12 at 14:45
Using dynamic SQL that way is usually not safe (SQL injection). – RBarryYoung Apr 09 '12 at 17:45

score 0 · Answer 4 · edited May 23 '17 at 12:06

0

You will have to code it yourself, using the xml sql data type is a good choice.

See: Passing an array of parameters to a stored procedure for a code sample.

edited May 23 '17 at 12:06

Community

1
1

answered Apr 09 '12 at 14:43

jorgebg

6,560
1
22
31

Royi Namir · Answer 5 · 2012-04-09T17:28:37.460

0

send XML to SQL

            List<string> lst = new List<string> {
                                                        "1",
                                                        "2",
                                                        "3"
                                                };

            XmlSerializerNamespaces namespaces = new XmlSerializerNamespaces(); 
            namespaces.Add(string.Empty, string.Empty);
            StringBuilder sb = new StringBuilder();
            using (var sw = new StringWriter(sb)) //serialize
            {
                var serializer = new XmlSerializer(typeof (List<string>));
                serializer.Serialize(sw, lst, namespaces);
            }

now , send SB to sql as param.

thats all.

dont use CSV.

edited Apr 09 '12 at 17:28

answered Apr 09 '12 at 14:49

Royi Namir

144,742
138
468
792

Should not I use `SQlBulkCopy`? Thus, send all records in database table and join it in your Stored Proc. Initially in the Parameters list send a unique GUID to Stored Proc and also send this same GUID in the table to keep track of associated records. Once the Job is done in Stored Proc, just delete the associated record from table before elaving. What do you say ? – Pankaj Apr 09 '12 at 15:28
I dont see any relation between Bulk Copy. Bulk Copy just dont save the insertions in LOG file. Dont use CSV values ( like others has suggested). use the common format : XML. – Royi Namir Apr 09 '12 at 17:26

Passing a List to a stored procedure

5 Answers5