How to Pass a Table-Value Parameter

Pass test data to table-valued parameter within SQL

You can't use a temp table - you have to use a table variable:

declare @t [MyNameSpace].[MyTypeTable]
insert into @t (/*columns*/) values
(/* first row */),
(/* second row */)

EXECUTE MyNameSpace.MyStoredProc @MyTypeTableVar = @t;

(You can populate it with either INSERT ... VALUES as shown above or INSERT ... SELECT if you have an existing table containing the data you care about)

Passing DataTable to Table-Valued Parameter in stored procedure not working

As rightly pointed out by @Alexander Petrov , you are declaring a table type with name dbo.filters

CREATE TYPE dbo.Filters AS TABLE

but in stored procedure definition you are passing a table variable of type dbo.filtros

ALTER PROCEDURE [dbo].[uspPesquisarReceita] (@TVP dbo.Filtros READONLY)

it should be of type dbo.filters.

Moreover, always be very careful when using both AND and OR operator in where clause without parentheses " ( ) ", as due to precedence of AND over OR in SQL, your intended condition might not give you expected results.

Let me explain, below is the where which you have used

WHERE R.IDEstado = 1 AND C.NomeCategoria IN (SELECT Categoria FROM @TVP) OR DF.Dificuldade IN (SELECT Dificuldade FROM @TVP) OR D.Duracao IN (SELECT Duracao FROM @TVP)

if you run your query, in your result set you will see results where R.IDEstado = 1 and C.NomeCategoria have a value from Categoria column in @TVP, both of these have to be true. After this AND is evaluated other OR condition will be evaluated.

but, you might want your result with first condition

R.IDEstado = 1

to be mandatorily true and any one or more than one conditions from

C.NomeCategoria IN (SELECT Categoria FROM @TVP) OR DF.Dificuldade IN (SELECT Dificuldade FROM @TVP) OR D.Duracao IN (SELECT Duracao FROM @TVP)

should be true, however you are not clearly getting that from your query as explained above.

So you should use the where as below, all OR conditions enclosed in Parentheses:

WHERE R.IDEstado = 1 AND (C.NomeCategoria IN (SELECT Categoria FROM @TVP) OR DF.Dificuldade IN (SELECT Dificuldade FROM @TVP) OR D.Duracao IN (SELECT Duracao FROM @TVP))

Note: You might have used AND and OR conditions as indented in your query, I am just explaining the implications using AND and OR condition without Parentheses in where clause which may or may not affect your case.

Is it possible to pass values to a table type parameter from PYODBC to SQL Server?

pyodbc.ProgrammingError: ('The SQL contains 1 parameter markers, but 2 parameters were supplied', 'HY000')

You are getting that error because a table-valued parameter is a list of iterables (preferably tuples) ...

my_tvp = [('Hello!', 1), ('Goodbye!', 2)]
print(f"my_tvp contains {len(my_tvp)} row(s)")
# my_tvp contains 2 row(s)

... and if you pass that directly to .execute() then each row is interpreted as a parameter value:

sql = "{CALL TestTypeProcedure (?)}"
params = my_tvp
print(f"calling SP with {len(params)} parameter value(s)")
# calling SP with 2 parameter value(s)
crsr.execute(sql, params)  # error

Therefore, you need to wrap your tvp inside a tuple to make it a single parameter value

sql = "{CALL TestTypeProcedure (?)}"
params = (my_tvp, )  # tuple containing a single tvp "object"
print(f"calling SP with {len(params)} parameter value(s)")
# calling SP with 1 parameter value(s)
crsr.execute(sql, params)  # no error

How to pass a table-value parameter

You can pass the parameter as a DataTable, IEnumerable<SqlDataRecord>, or DbDataReader.

Can I pass table-valued parameters agnostically?

Is there a way I can pass a table-valued parameter through ADO.NET without resorting to a concrete SQL ADO implementation

No.

You'll have to downcast to SqlParameter when doing SQL Server-specific stuff, like Table-Valued Parameters. eg

var param = command.AddParameter("@table", dt);
var sqlParam = (SqlParameter) param;
sqlParam.SqlDbType = SqlDbType.Structured;  
sqlParam.TypeName = "dbo.CategoryTableType";

Of course you could use Reflection, but that would just be a waste of time.

Is it better to use Custom TABLE TYPE as parameter instead of SQL IN clause when passing a large comma separated value

As this question did raise quite some discussion in comments but did not get any viable answer, I'd like to add the major points in order to help future research.

This question is about: How do I pass a (large) list of values into a query?

In most cases, people need this either in a WHERE SomeColumn IN(SomeValueList)-filter or to JOIN against this with something like FROM MyTable INNER JOIN SomeValueList ON....

Very important is the SQL-Server's version, as with v2016 we got two great tools: native STRING_SPLIT() (not position-safe!) and JSON support.

Furthermore, and rather obvious, we have to think about the scales and values.

Do we pass in a simple list of some IDs or a huge list with thousands of values?
Do we talk about simple integers or GUIDs?
And what's about text values, where we have to think about dangerous characters (like [ { " in JSON or < & in XML - there are many more...)?
What about CSV-lists, where the separating character might appear within the content (quoting / escaping)?
In some cases we might even want to pass several columns at once...

There are several options:

Table valued parameter (TVP, CREATE TYPE ...),
CSV together with string splitting functions (native since v2016, various home brewed, CLR...),
and text-based containers: XML or JSON (since v2016)

Table valued paramter (TVP - the best choice)

A table valued parameter (TVP) must be created in advance (this might be a draw back) but will behave as any other table once created. You can add indexes, you can use it in various use cases and you do not have to bother about anything under the hood.

Sometimes we cannot use this due to missing rights to use CREATE TYPE...

Character separated values (CSV)

With CSV we see three approaches

Dynamic Sql: Create a statement, where the CSV list is simply stuffed into the IN() and execute this dynamically. This can be a very efficient approach, but will be open to various obstacles (no ad-hoc-usage, injection threat, breaking on bad values...)
String splitting functions: There are tons of examples around... All of them have in common that the separated string will be returned as a list of items. Common issues here: performance, missing ordinal position, limits for the separator, handling of duplicate or empty values, handling of quoted or escaped values, handling of separators within the content. Aaron Bertrand did some great research about the various approaches of string splitting. Similar to TVPs one draw back might be, that this function must exist in the database in advance or that we need to be allowed to execute CREATE FUNCTION if not.
ad-hoc-splitters: Before v2016 the most used approach was XML based, since then we have moved to JSON based splitters. Both use some string methods to transform the CSV string to 1) separated elements (XML) or 2) into a JSON-array. The result is queried by 1) XQuery (.value() and .nodes()) or 2) JSON's OPENJSON() or JSON_VALUE().

Text based containers

We can pass the list as string, but within a defined format:

Using ["a","b","c"] instead of a,b,c allows for immediate usage of OPENJSON().
Using <x>a</x><x>b</x><x>c</x> instead allows for XML queries.

The biggest advantage here: Any programming language provides support for these formats.

Common obstacles like date and number formatting is solved implicitly. Passing JSON or XML is - in most cases - just some few lines of code.

Both approaches allow for type- and position-safe queries.

We can solve our needs without the need to rely on anything existing in advance.

Is it possible to pass in Table-Valued Parameters to PYODBC query?

As mentioned in a comment to the question, it is possible to pass a TVP to an anonymous code block from C# using System.Data.SqlClient like so:

var tvpData = new DataTable();
tvpData.Columns.Add(new DataColumn("id", Type.GetType("System.Int32")));
tvpData.Rows.Add(new object[] { 1 });
tvpData.Rows.Add(new object[] { 2 });

using (var con = new SqlConnection(@"Server=127.0.0.1,49242;Database=mydb;Trusted_Connection=True;"))
{
    con.Open();
    using (var cmd = new SqlCommand("SELECT * FROM @p1", con))
    {
        SqlParameter tvpParam = cmd.Parameters.AddWithValue("@p1", tvpData);
        tvpParam.SqlDbType = SqlDbType.Structured;
        tvpParam.TypeName = "dbo.dboListInt";  // an existing user-defined table type

        SqlDataReader rdr = cmd.ExecuteReader();
        Console.WriteLine("rows returned:");
        while (rdr.Read())
        {
            Console.WriteLine(rdr[0]);
        }
    }
}

console output:

rows returned:
1
2

However, that won't work with an ODBC connection from C# using System.Data.Odbc because the code relies on the SqlClient-specific SqlDbType.Structured type.

pyodbc does not have a similar mechanism for specifying a "structured" (TVP) parameter type. Instead, it deduces the presence of a table-valued parameter by the "shape" of the parameters it receives (e.g., a row represented by a tuple of elements, one of which is itself a list of tuples).

Furthermore, the conversation between pyodbc and the ODBC driver currently does not fully account for user-defined types as parameters for anything other than a normal stored procedure call. (It would be nice if we could use a temporary stored procedure — CREATE PROCEDURE #myTempSP … — but testing indicates that temporary stored procedures cannot work with user-defined table types.)

TL;DR — TVPs can be passed to an anonymous code block from C# but (currently) not from Python via pyodbc.