How to pass an array of int to stored procedure then select all where id is in that array

Question

I'm creating a web api method where i could select all products under a given list of category id's as parameter

c# method would be:

        [Route("api/products/search")]
        public HttpResponseMessage SearchProducts([FromUri] int[] categoryIds)
        {
        DataSet ds = new DataSet();
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[connection_string].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("sp_search_product_by_category", con);
            cmd.Parameters.AddWithValue("category_ids", categoryIds);
            cmd.CommandType = CommandType.StoredProcedure;

            con.Open();
            SqlDataAdapter adp = new SqlDataAdapter();
            adp.SelectCommand = cmd;
            adp.Fill(ds);
        }

        var message = Request.CreateResponse(HttpStatusCode.OK, ds.Tables[0]);
        return message;
        }

And then sp would be:

CREATE PROCEDURE [dbo].[sp_search_product_by_category] 
    -- Add the parameters for the stored procedure here
    @category_ids [some_array_data_type] <-- what should be in here?
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Select statements for procedure here
    SELECT * from products where category in @category_ids
END

How would i let the SP know that its an array, and make it select appropriately?

Any help would be appreciated

I think you've just answered to your question with this line - _`@category_ids [some_array_data_type]`_ ([some_array_data_type] is your user defined table type) — SᴇM, Feb 15 '18 at 08:23
The duplicate shows how to pass an array. Another option is to use [Dapper](https://github.com/StackExchange/Dapper) to execute the query and pass a list of IDs, eg `connection.Query("select * from products where category in @category_ids",categoryIds);`. Dapper will create a safe `IN (1,2,56,...)` clause from the array values — Panagiotis Kanavos, Feb 15 '18 at 08:41
BTW you *don't have to* and *shouldn't* return DataTables from a Web API method. Users expect a JSON payload that contains an array of `Product` objects with a specific schema. It's a lot easier and *cheaper* to return a list of products that create an expensive DataTable then serialize it to JSON and handle the unexpected schema — Panagiotis Kanavos, Feb 15 '18 at 08:44

How to pass an array of int to stored procedure then select all where id is in that array

0 Answers0