CODEWITHSUNDEEP
.netsql-servertable-variable
Steve Hood
Steve Hood

Reputation: 88

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

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!!!

Upvotes: 3

Views: 1676

Answers (1)

Eric J. Price
Eric J. Price

Reputation: 2785

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.

Upvotes: 2

Related Questions