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

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

Solution

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

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