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

Question

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

`N'EXEC dbo.yy_StoredProc'` you aren't passing a parameter to `yy_StoredProc`... — Thom A, Jul 10 '23 at 13:34
@ThomA I thought lines two and three of Output #2 is passing the parameter? If it's not, how do I pass it correctly? — LuckeyKays22, Jul 10 '23 at 13:37
You're passing the parameter to `sys.sp_executesql` yes, but you're not passing *any* parameters to `dbo.yy_StoredProc`. — Thom A, Jul 10 '23 at 13:41
Ahhh I can't believe how simple that is. Thank you so much. I was able to get it to return records! — LuckeyKays22, Jul 10 '23 at 13:51

score 3 · Accepted Answer · answered Jul 10 '23 at 13:49

3

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

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

answered Jul 10 '23 at 13:49

Stu

30,392
6
14
33

1

Similar problem here https://stackoverflow.com/a/67911423/14868997 and here https://stackoverflow.com/a/73251033/14868997 it's because the command type is wrong – Charlieface Jul 10 '23 at 14:03
Yes adding the parameter to the procedure line worked. I was thinking lines 2 and 3 were passing it, but as stated above, that only passes it to the sp_executesql. Thank you for your answer and help! – LuckeyKays22 Jul 10 '23 at 17:23

score 1 · Answer 2 · answered Jul 10 '23 at 14:00

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.

We have CommandType.StoredProcedure. The problem was not having the parameter in the exec line as the answer above shows. Thank you so much for your input though! I appreciate it! — LuckeyKays22, Jul 10 '23 at 18:33
Doesn't make sense: if you were using `CommandType.StoredProcedure` then it wouldn't use `sp_executesql` to execute it, and you wouldn't need to add it to the command text — Charlieface, Jul 11 '23 at 00:19

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

2 Answers2