CODEWITHSUNDEEP
asp.netsql-serverstored-procedures
chamara
chamara

Reputation: 12709

Passing a List<string> to a stored procedure

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

Upvotes: 3

Views: 7898

Answers (5)

Royi Namir
Royi Namir

Reputation: 148744

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.

Upvotes: 0

JotaBe
JotaBe

Reputation: 39075

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

Upvotes: 1

Steve Stedman
Steve Stedman

Reputation: 2672

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!

Upvotes: 6

jorgebg
jorgebg

Reputation: 6600

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.

Upvotes: 0

Patrick
Patrick

Reputation: 17973

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.

Upvotes: 0

Related Questions