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
– Thom A ♦, Commented Jul 10, 2023 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
– LuckeyKays22, Commented Jul 10, 2023 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
– Thom A ♦, Commented Jul 10, 2023 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
– LuckeyKays22, Commented Jul 10, 2023 at 13:51

Stu · Accepted Answer · 2023-07-10 13:49:47Z

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, 2023 at 13:49

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charlieface Over a year ago

Similar problem here stackoverflow.com/a/67911423/14868997 and here stackoverflow.com/a/73251033/14868997 it's because the command type is wrong

LuckeyKays22 Over a year ago

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!

Charlieface · Accepted Answer · 2023-07-10 14:00:48Z

1

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.

answered Jul 10, 2023 at 14:00

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

2 Comments

LuckeyKays22 Over a year ago

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!

Charlieface Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related