CODEWITHSUNDEEP
c#sql-serverstored-proceduresdappertable-valued-parameters
Rahul
Rahul

Reputation: 2307

Pass table-valued parameter to a stored procedure

In my project we are using Dapper to connect with database for operations.

I have created a stored procedure in my SQL Server database as:

CREATE PROCEDURE dbo.usp_Check
    @TestTableType [dbo].[TestTableType] READONLY
AS
BEGIN
  SELECT 
      TestTableType.BrokerNPN,
      CASE WHEN Sales.BrokerNPN IS NULL THEN 0 ELSE 1 END as isBrokerNPNExist
  FROM 
      [dbo].[tbl_Sales] Sales
  LEFT JOIN 
      @TestTableType TestTableType ON Sales.BrokerNPN = TestTableType.BrokerNPN
END

And in C# I am trying to consume my stored procedure with a TVP like this:

public void CheckSP(List<string> ParamData)
{
    using (var connection = new ConnectionProvider("DbName").GetOpenConnection())
    {
        var dt = new DataTable("dbo.TestTableType");
        dt.Columns.Add("NPN", typeof(string));
        dt.Rows.Add("12345");

        // First attempt
        var result = connection.Query<CheckData>("usp_Check", new { BrokerNPN = ParamData }, CommandType.StoredProcedure).ToList();

        // Second attempt
        var result = connection.Query<CheckData>("usp_Check", new { BrokerNPN = dt}, CommandType.StoredProcedure).ToList();
    }
}

But I am not able to pass the TVP to the stored procedure.

For the first attempt, I am getting this error

Procedure or function CheckBrokerNPNExist has too many arguments specified

And for the second attempt, I cannot use pass DataTable directly.

Upvotes: 1

Views: 4652

Answers (2)

marc_s
marc_s

Reputation: 754628

I think you're just using a wrong name for the parameter...

See your stored procedure definition:

CREATE PROCEDURE dbo.usp_Check
    @TestTableType [dbo].[TestTableType] READONLY

Your parameter is called @TestTableType - yet when you call this stored procedure from C#, you're using a different name:

var result = connection
                .Query<CheckData>("usp_Check", 
                                  new { BrokerNPN = ParamData }, 
                                  CommandType.StoredProcedure).ToList();

I would try to use the same name: 

var result = connection
                .Query<CheckData>("usp_Check", 
                                  new { TestTableType = ParamData }, 
                                  CommandType.StoredProcedure).ToList();

Upvotes: 0

Rasna
Rasna

Reputation: 1

Use the same column name in [dbo].[TestTableType] in c# code

Upvotes: 0

Related Questions