How to make a stored procedure in which the parameters are a list of strings [ SQL Server, ASP.NET Core 3.1 ]

Question

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

you could pass a list of strings as a table-valued parameter. — Stu, Feb 06 '22 at 18:54
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Feb 06 '22 at 19:02
Perhaps use a [Table Valued Parameter](https://stackoverflow.com/questions/8157345/entity-framework-stored-procedure-table-value-parameter) or JSON or XML, — Charlieface, Feb 06 '22 at 20:39

score 2 · Answer 1 · answered Feb 08 '22 at 06:07

Here's an example using table value parameters;

CREATE TYPE [TableString] AS TABLE (
   Id nvarchar(4000) NULL
)
go
create procedure [name]
  @brands [TableString],
  ...
as
...

    public SqlParameter ToSqlParameter(IEnumerable<string> values)
    {
        var meta = new SqlMetaData("Id", SqlDbType.NVarChar, 4000);
        var records = values.Select(v => {
            var record = new SqlDataRecord(
                meta
            );
            if (v == null)
                record.SetDBNull(0);
            else
                record.SetString(0, v);
            return record;
        });
        return new SqlParameter()
        {
            TypeName = "TableString",
            SqlDbType = SqlDbType.Structured,
            Value = records
        };
    }

Now you can define that your procedure takes a TableString argument.

You can extend this example slightly, so that you can pass in a TableString into a query;

public class TableValue<T>
{
    public T Id { get; set; }
}

public class MyContext : DbContext {
    protected override void OnModelCreating(ModelBuilder modelBuilder) {
    ...
        modelBuilder.Entity<TableValue<string>>(e =>
        {
            e.HasNoKey();
            e.ToView("TableString");
            e.Property(p => p.Id).HasMaxLength(4000);
        });
    }
}

var queryable = context.Set<TableValue<string>>()
    .FromSqlInterpolated($"select * from {ToSqlParameter( ... )}");

score 1 · Accepted Answer · edited Feb 08 '22 at 03:07

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

How to make a stored procedure in which the parameters are a list of strings [ SQL Server, ASP.NET Core 3.1 ]

2 Answers2