Passing a list of integers to a stored procedure.

TSQL stored procedures don't support arrays as input parameters, so in order to pass a list we have to convert it to a supported type and then convert it back to a list. One solution is to serialize the List<int> into an xml string, pass it as nText and then use the SQL Server xml capabilities to transform it into a table.

List<int> results = new List<int>();

XmlSerializer xs = new XmlSerializer(typeof(List<int>));

StringWriter sw = new StringWriter(new StringBuilder());

xs.Serialize(sw, results);

PatientResultsTableAdapter ta = new PatientResultsTableAdapter();

table = ta.GetDataByPatientBins(sw.ToString());


CREATE PROCEDURE [dbo].[SelectList]
    @ids nText
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @hDoc int
 
    --Prepare input values as an XML documnet

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @ids

    select *
    from results
    where result_id in (select id from openxml (@hDoc,
'/ArrayOfInt/int') with (id int 'text()'))
                        

    EXEC sp_xml_removedocument @hDoc
END