How to create a stored procedure with dynamically created IN set

Question

I have some SQL that I am converting to stored procedures via blind requirement/request. There is a bit of SQL in the application I'm looking at that builds the where clause with a dynamic IN (set) statement. I have searched for dynamic stored procedure but nothing close to what I'm looking for has come up for me. Here is a sample of the WHERE clause:

WHERE Var.A = @Param AND Var.Id IN

From here the SQL is built manually using a string builder and then executed. Not sure how I'd convert this into a stored procedure as I'm fairly new to them.

We are using C# and SQL Server

Answer 1

You could use an user-defined data type.

On the C# side it would look like this:

//Setup a DataTable with the same structure as the SQL Type
var data = new DataTable();
data.Columns.Add("value", typeof(string));

//Populate the table
data.Rows.Add("oneID");
data.Rows.Add("anotherID");

//You create your sql command
cmd.Parameters.Add("@listArgument", data);
//Command execution

On the SQL side you could have a type like this

CREATE TYPE [dbo].[NVarCharTable] AS TABLE (
    [value] NVARCHAR(MAX) NOT NULL);

And then the Stored procedure:

CREATE PROCEDURE [dbo].[MyProc] 
    @listArgument NVarCharTable READONLY
AS
BEGIN

    SELECT *
    FROM FOO
    WHERE Var.Id IN (Select [value] FROM @listArgument)

END

Reference: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

Answer 2

If you are using SQl SERVER 2016 or above you can use the string_split function to convert the csv params into table and then use it in your IN list

e.g.

SELECT * FROM TBL WHERE Var.A = @Param AND Var.Id IN (SELECT value FROM STRING_SPLIT(@inlist, ','))

Hope this helps