How to make a stored procedure in which the parameters are a list of strings [ SQL Server, ASP.NET C-CodePudding

I am creating a website where users can filter the data by brand, storage, price, etc. I want to use a stored procedure to get the data. The problem is that I don't know how to make a stored procedure that accepts a list of strings as parameters.

For example, this is my ASP.NET Core controller:

    private readonly contextItem _context;

    public ItemsController(contextItem context)
    {
        _context = context;
    }

    // GET: api/Items
    [HttpGet]
    public async Task<ActionResult<IEnumerable<Item>>> GetItem(
        string samsung, string iphone, string lg, string _32gb, string _64gb,string _128gb,
        string max, string min)
    {
        List<string> brands = new List<string>(), storage = new List<string>();
        string price_min = null, price_max = null;

        // brands
        if (samsung != null) { brands.Add("samsung"); }
        if (iphone != null) { brands.Add("iphone"); }
        if (lg != null) { brands.Add("lg"); }

        // storage
        if (_32gb != null) { storage.Add("32gb"); }
        if (_64gb != null) { storage.Add("64gb"); }
        if (_128gb != null) { storage.Add("128gb"); }

        // price
        price_max = max != null ? max : "";
        price_min = min != null ? min : "";

        string query = $"EXEC SP_Filter @brand ='{string.Join(",", brands)}',@storage='{string.Join(",", storage)}',"  
            $"@min='{price_min}',@max='{price_max}'";

        return await _context.Item.FromSqlRaw(query).ToListAsync();
    }

So the parameters can be more than one, but if they weren't, it would be easy to make a stored procedure like this

create procedure SP_Filter
    @brands varchar(250),
    @storage varchar(250),
    @min decimal,
    @max decimal
as
    Select * 
    From Item
    Where NAME like '%' @brands '%' 
      and NAME like '%' @storage '%' 
      and PRICE between @min and @max

CodePudding user response：

You can use json or xml. Try this

/* test

DECLARE @Brands nvarchar(4000) SET @Brands = '{"Brands": [{"Brand": "Samsung"}, {"Brand": "iPhone"}, {"Brand": "LG"}, {"Brand": "Xiaomi"}]}';;

DECLARE @Storages nvarchar(4000) SET @Storages = '{"Storages": [{"Storage": "16"}, {"Storage": "32"}, {"Storage": "64"}, {"Storage": "128"}]}';;

SELECT * FROM OPENJSON(@Brands, '$.Brands') WITH ( Brand varchar(20) '$.Brand' );
SELECT * FROM OPENJSON(@Storages, '$.Storages') WITH ( Storage varchar(20) '$.Storage' );

*/

    
        create procedure FilterByBrandStoragePrice
            @Brands             nvarchar(4000),
            @Storages           nvarchar(4000),
            @min                decimal,
            @max                decimal
        as
            SELECT * 
            FROM Item i
            WHERE i.Brand in (SELECT * FROM OPENJSON(@Brands, '$.Brands') WITH ( Brand varchar(20) '$.Brand' )) 
            AND i.Storage in  (SELECT * FROM OPENJSON(@Storages, '$.Storages') WITH ( Storage varchar(20) '$.Storage' ))
              AND i.PRICE between @min and @max

CodePudding user response：

You can send your data as JSON or XML, or even concatenate values with a delimiter and split it from stored procedure.