Passing table variable from C# to SQL Server passes empty table

Question

Calling a proc from C# (.NET 4.5, Visual Studio 2013) it passes table variables using sp_executesql. However, SQL Server (tested on 2008 Std, 2008 Ent, and 2012 Std) passes this through as an empty table.

I would expect the three statements here to return the same results, but the last one does not.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_testproc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_testproc]
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'testType' AND ss.name = N'dbo')
DROP TYPE [dbo].[testType]
GO
--------------------------------
CREATE TYPE [dbo].[testType] AS TABLE(
    [ServerID] [int] NULL
    , [Field2] int NOT NULL
)

GO
---------------------------------
CREATE PROC _testproc
    @testTable testType READONLY 
AS

SELECT * FROM @testTable 

GO
---------------------------------
declare @x testtype

INSERT INTO @X values (1,2)
INSERT INTO @X values (3,4)

--Begin Three calls that should return the same result
--Query the table directly
SELECT * FROM @x

--Call it the way I would through t-sql
exec _testproc @testTable = @x

--Call it the way C# in Visual Studio 2013 calls it
exec sp_executesql N'dbo._testproc',N'@testTable [dbo].[TestType] READONLY',@testTable=@x

--Cleanup
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_testproc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[_testproc]
GO

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'testType' AND ss.name = N'dbo')
DROP TYPE [dbo].[testType]
GO

The obvious answer to me would be to not use sp_executesql with table variables in a proc, but this C# code does exactly that.

using (SqlCommand cmd = new SqlCommand("dbo._testproc", connCentral))
            {
                SqlParameter sqlpTestTable = cmd.Parameters.AddWithValue("@TestTable", dtTestTable);
                sqlpTestTable.SqlDbType = SqlDbType.Structured;
                sqlpTestTable.TypeName = "dbo.TestType";

                using (SqlDataAdapter aTest = new SqlDataAdapter())
                {
                    aTest.SelectCommand = cmd;

                    aTest.Fill(dsTest2, "Test2");
                }
            }

Any help you can give me would be greatly appreciated. Thanks!!!

Eric J. Price · Accepted Answer · 2013-12-19 23:02:58Z

2

Change...

exec sp_executesql N'dbo._testproc',N'@testTable [dbo].[TestType] READONLY',@testTable=@x

To...

exec sp_executesql N'dbo._testproc @testTable',N'@testTable [dbo].[TestType] READONLY',@testTable=@x

I tested it, it works. You're passing your variable to the executed command text, but you don't use the variable in your command text.

answered Dec 19, 2013 at 23:02

Eric J. Price

2,7851 gold badge17 silver badges22 bronze badges

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

1 Comment

Steve Hood Over a year ago

This does work from both SQL Server and .NET, and thank you. However, I would add that this is not how you typically pass parameters from .NET code. At this point I feel as though I'm using a workaround instead of proper code, although I'm extremely happy to have this!!! using (SqlCommand cmd = new SqlCommand("dbo._testproc @testTable", connCentral))

Collectives™ on Stack Overflow

Passing table variable from C# to SQL Server passes empty table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related