Evil Kraig F (1/3/2013)
So, I'm currently looking over some 2k5 to 2k8 upgrades we're doing (yeah, I know) and I'm reviewing using table parameters. I'm having a bit of confusion over the implementation.Now, I know within the same server that types are passed byref instead of byval and thus cannot travel between different databases. That alone is a headscratching design decision but whatever.
Now, what I'm trying to do is cleanup a linked server call that is currently doing loop-the-loops back to the originating DB for some information. Now, 2k5 you'd use XML and pass that along to your foreign procedure to use in data limitations, but that's not a simple technique. So what's happening is it's calling the foreign proc, which calls back for a data limiter in a local #tmp, which it then uses to send information back to the originating structure. This is necessary else the foreign call would dump way too much data back to be limited after the fact.
What I figured I'd do was implement the new table variable parameters and just send it along in the call, but for the life of me I can't figure out how to byval the pass. The .NET calls off the sqlclient library almost have to pass byval, as they can't send their byref down the connection pipe, unless I'm seriously misunderstanding something about that connection object.
Can anyone point out to me what I've missed?
Not sure I am completely getting the concern, but I thought I would trace a stored proc call from .NET and show what SQL the SqlClient actually generates on the server side whenever a TVP is used. I had done this once before when TVPs first were released but the refresher was nice.
Reference: http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters
USE [SampleDB]
GO
/****** Object: UserDefinedTableType [dbo].[SampleDataType] Script Date: 1/5/2013 4:50:50 PM ******/
CREATE TYPE [dbo].[SampleDataType] AS TABLE(
[SampleString] [nvarchar](64) NOT NULL,
[SampleInt] [int] NULL
)
GO
[font="Courier New"]//in this example we create a data table with same name as the type we have in the DB
DataTable dataTable = new DataTable("SampleDataType");
//we create column names as per the type in DB
dataTable.Columns.Add("SampleString", typeof(string));
dataTable.Columns.Add("SampleInt", typeof(Int32));
//and fill in some values
dataTable.Rows.Add("99", 99);
dataTable.Rows.Add("98", null);
dataTable.Rows.Add("97", 99);
SqlConnection sqlConnection =
new SqlConnection(
ConfigurationManager.ConnectionStrings["SampleDBConnectionString"].ConnectionString);
SqlCommand command = sqlConnection.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "[dbo].[SampleProcedure]";
SqlParameter parameter = new SqlParameter();
//The parameter for the SP must be of SqlDbType.Structured
parameter.ParameterName="@Sample";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = dataTable;
command.Parameters.Add(parameter);
sqlConnection.Open();
int numberOfRowsUpdated = command.ExecuteNonQuery();[/font]
declare @p1 dbo.SampleDataType
insert into @p1 values(N'99',99)
insert into @p1 values(N'98',NULL)
insert into @p1 values(N'97',99)
exec [dbo].[SampleProcedure] @Sample=@p1
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato