Passing list <string> to stored procedure
I have a list of strings and I need to check if any of the values in the list contain in a database table. If exists, return a dataset 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
CREATE PROCEDURE spCheckDocNumber
@DocNumber VARCHAR(MAX)
AS
BEGIN
SELECT * FROM tblDocumentHeader WHERE DocumentNumber = @DocNumber
END
I need to know how I can pass a list to a stored procedure and how to check the list in the procedure. Help plz
source to share
Form a Split function that splits a string based on 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 the comma, then you can use the output as a table, which is then concatenated to the table you are looking for.
This makes it easy to split the comma-separated list. Then you can simply pass a string with all the hte values separated by comma.
Hope this helps!
source to share
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 should call this procedure with a parameter like this:
exec IGetAListOfValues
@List = '<params> <p>string1</p> <p>string2</p> </params>' -- xml parameter
The nodes function uses the xPath expression. In this case, it is /params/p
, so XML is used <params>
as root and <p>
- as element.
For more information see this answer: Passing a List of Stored Procedure Values
source to share
Perhaps you could use the B statement in sql instead. There are several tutorials on how to use this at http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm .
source to share
You will need to code it yourself using the xml sql datatype - a good choice.
See Passing an Array of Parameters to a Stored Procedure for a sample code.
source to share
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);
}
send SB to sql as parameter.
that's all.
don't use CSV.
source to share