CROSS JOIN from a parameter in SQL

I've successfully been able to implement a CROSS JOIN to my query, however, I am now interested in changing the contents of the first table in the cros开发者_如何学运维s join to be more dynamic. So I would like to store the first table as a variable, then perform a CROSS JOIN between my variable table and another table.

Is there anyway to implement this behavior? How can I take a C# DataTable and put it into a table declaration in SQL though?

Has anyone ever tried anything like this before?

You can pass a DataTable to a sproc as a Table-valued parameter

Depending on what you're doing you may also want to look into table-valued functions and APPLY

You need to build a stored procedure (or more than one).

Inside a MS SQL (2005 and up, I think) stored procedure you can create a table variable like below:

DECLARE @tablevar table (
   ID int not null,
   Name nvarchar(100) not null,
   <rest of your columns>
)

In SQL 2008, you can also pass a DataTable as a parameter to an SP from .NET code. See http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters for a good how-to (including VB.NET example code).