CODEWITHSUNDEEP
sql-serverstored-proceduressp-executesql
LuckeyKays22
LuckeyKays22

Reputation: 13

sp_executesql creating issue when passing a data table from C# to SQL Server

I have been trying to send a DataTable from C# to SQL Server. We have narrowed down the problem to execution of code in SQL Server.

Below is the setup for the testing code developed.

DROP PROCEDURE [dbo].[yy_StoredProc]
GO

DROP TYPE [dbo].[DataTableType2]
GO

CREATE TYPE [dbo].[DataTableType2] AS TABLE
       (
           [Street] [varchar](100) NULL,
           [City] [varchar](100) NULL,
           [State] [varchar](100) NULL,
           [Country] [varchar](100) NULL
       )
GO

CREATE PROCEDURE [dbo].[yy_StoredProc]
    @PassedMvcTable DataTableType2 READONLY
AS
BEGIN
    SELECT * FROM @passedMvcTable
END
GO

The code below is modeled after that we retrieved with Profiler. Output #1 and Output #3 return the data in @p3. However, Output #2 does not return any data.

Why do outputs #1 and #3 work, but output #2 doesn’t?

declare @p3 DataTableType2
insert into @p3 values('International Dr', 'Orlando', 'FL', 'USA')

--OUTPUT #1
select * from @p3

--OUTPUT #2
exec sp_executesql N'EXEC yy_StoredProc',
        N'@PassedMvcTable [DataTableType2] READONLY',
        @passedMvcTable=@p3

--OUTPUT #3
exec yy_StoredProc @passedMvcTable=@p3

Upvotes: 1

Views: 204

Answers (2)

Charlieface
Charlieface

Reputation: 71544

I can't see your C# code, but you are almost certainly creating the command incorrectly.

You need to create it with CommandType.StoredProcedure and just pass the name of the procedure, not EXEC...

using var conn = new SqlConnection(ConnStringHere);
using var comm = new SqlCommand("dbo.yy_StoredProc", conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@PassedMvcTable", SqlDbType.Structured)
    {
        TypeName = "dbo.DataTableType2",
        Value = GetDataTableForTVPHere(),
    });

using var reader = comm.ExecuteReader();
// etc

Note that what the profiler shows for INSERT statements into the TVP doesn't actually happen like that. It's just a text representation of what's going on under the hood.

Upvotes: 1

Stu
Stu

Reputation: 32599

You're simply missing passing the parameter to the procedure:

exec sp_executesql 
  N'EXEC yy_StoredProc @PassedMvcTable',  
  N'@PassedMvcTable DataTableType2 READONLY',
  @passedMvcTable=@p3

Upvotes: 3

Related Questions