How to Pass Table Value Parameters to Stored Procedure from .Net Code

How to call stored procedure with table valued parameter from .net core Entity Framework

Finally I could able to call Table values parameter Stored procedure from my .net core service which uses EF core

I have created fake Response Domain Model

   public class MyResponse
    {
        public int Id { get; set; }
        public int Col1{ get; set; }
        public int Col2 { get; set; } 
    }

I called it Fake Domain Model because Domain model is generally table representation in CLR class but I dont have such Table in SQL Server.

Then I have Added property for this under Context class as below

public class MyQueryDbContext : DbContext
{
  public virtual DbSet<MyResponse> Data { get; set; }
    .
    .
    .
}

Then Need to create Data Table for Request parameter list, I have added like below

 DataTable table = new DataTable();
 table.Columns.Add("Id", typeof(int));
 table.Columns.Add("Col1", typeof(int));
 table.Columns.Add("Col2", typeof(int));

Then I have called Stored procedure from .net core API using EF and passed datatable as parameter like below

 var parameter = new SqlParameter("@MyRequest", SqlDbType.Structured);
 parameter.Value = table;
 parameter.TypeName = "[dbo].[MyRequestType]" // My Table valued user defined type
var response=_context.Data
                     .FromSql("EXEC [dbo].[GetMyData] @MyRequest", parameter)
                     .ToList()

You will get response in you response variable.

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.

SSIS how to pass Table-Value parameter into stored procedure

I'm not sure how much less verbose this option would be than a script task, but if you need use a table valued parameter to pass data into a into a stored procedure, after working with this data in multiple components in the package, you could load the data to a global temp table to do whatever processing is necessary. Once you're ready to send the data into the stored procedure using the table valued parameter, populate a T-SQL variable of the same type as the TVP and execute the stored procedure with this variable as the TVP parameter. In order to do this, make sure to place all the related tasks in a Sequence Container that has the TransactionOption set to Required. The components can either have their TransactionOption at Supported or set to Required as well. If all the components in the package are related to this specific objective/data, the Sequence container can be omitted and the same TransactionOption setting would apply to the package instead. If this approach works for you, you may want to consider removing the TVP from the stored procedure and just using the temp table. Below is an example of an Execute SQL Task that populates a table type variable from the temp table, then executes the stored procedure with this as a parameter. This will work with both OLE DB and ADO.NET connections

DECLARE @YourParameter AS IDs_TVP

INSERT INTO @YourParameter (ID)
SELECT ID FROM ##TempTable

EXEC dbo.TestSP @YourParameter

DROP TABLE ##TempTable

Pass table value type to SQL Server stored procedure via Entity Framework

Lets say you want to send a table with a single column of GUIDs.

First we need to create a structure using SqlMetaData which represents the schema of the table (columns).

The below code demonstrates one column named "Id" of the GUID is the SQL stored procedure parameter table type

var tableSchema = new List<SqlMetaData>(1)
{
  new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();

Next you create a list of records that match the schema using SqlDataRecord.

The below code demonstrates how to add the items inside a list using the above created schema. Create a new SqlDataRecord for each of the items in the list. Replace SetGuid with the corresponding type and Replace Guid.NewGuid() as the corresponding value.
Repeat new SqlDataRecord for each item and add them to a List

var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, Guid.NewGuid());
var table = new List<SqlDataRecord>(1)
{
  tableRow
};

Then create the SqlParameter:

var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds"; //@UserIds is the stored procedure parameter name
parameter.TypeName = "{Your stored procedure type name}"
parameter.Value = table;

var parameters = new SqlParameter[1]
{
  parameter
};

Then simply call the stored procedure by using the Database.SqlQuery.

IEnumerable<ReturnType> result;
using (var myContext = new DbContext())
{
  result = myContext.Database.SqlQuery<User>("GetUsers @UserIds", parameters)
    .ToList();         // calls the stored procedure
    // ToListAsync();  // Async
{

In SQL Server, create your User-Defined Table Type (I suffix them with TTV, Table Typed Value):

CREATE TYPE [dbo].[UniqueidentifiersTTV] AS TABLE(
  [Id] [uniqueidentifier] NOT NULL
)
GO

Then specify the type as a parameter (don't forget, Table Type Values have to be readonly!):

CREATE PROCEDURE [dbo].[GetUsers] (
  @UserIds [UniqueidentifiersTTV] READONLY
) AS
BEGIN
  SET NOCOUNT ON

  SELECT u.* -- Just an example :P
  FROM [dbo].[Users] u
  INNER JOIN @UserIds ids On u.Id = ids.Id
END

---

Related Topics