@Kenny recently tweeted the following: "it really is unfortunate that you can't supply a query as a parameter to a func/sproc that accepts a table parameter #sqlserver" Sounded like an interesting problem. I suggested creating a wrapper stored procudure where you passed in the query that you wanted to populate the TVP.
Following is a kludge that might work. Seems to work, but not sure if it is exactly what @Kenny was looking for. Interesting problem for sure!
The code is heavily based upon the sample included in the SQL 2008 BOL for
Table-Valued Parameters (Database Engine). The code is definitely on the quick and dirty side! Tested only on SQL Server 2008 Dev Ed.
USE AdventureWorks;
GO
DELETE [AdventureWorks].[Production].[Location]
WHERE LocationID NOT IN (SELECT LocationID FROM [AdventureWorks].[Production].[ProductInventory])
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'usp_InsertProductionLocation')
DROP PROCEDURE usp_InsertProductionLocation;
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'usp_TEST_TVP')
DROP PROCEDURE usp_TEST_TVP;
IF EXISTS (SELECT * FROM SYSTYPES WHERE NAME = 'LocationTableType')
DROP TYPE LocationTableType;
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate SMALLMONEY); -- changed from int to match table
GO
/* Create a procedure to receive data for the table-valued parameter. */
-- same as BOL
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks].[Production].[Location]
([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
-- NEW STUFF Follows:
-- wrapper sp
CREATE PROCEDURE usp_TEST_TVP
@SQL NVARCHAR(4000)
AS
BEGIN -- start of new sp
SET NOCOUNT ON;
-- must match table type
CREATE TABLE #temp
(LocationName VARCHAR(50),
CostRate SMALLMONEY);
DECLARE @EXECSQL nvarchar(4000);
SET @EXECSQL = 'INSERT INTO #temp (LocationName, CostRate) ' + @SQL;
EXECUTE(@EXECSQL);
/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT LocationName, CostRate
FROM #temp;
DROP TABLE #temp;
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
END -- end of new sp
GO
-- TEST CODE
-- Note: For this example the SELECT clause must match table
-- type and #temp Table structure - datatype, size and order.
-- BEFORE - should be empty
SELECT *
FROM [AdventureWorks].[Production].[Location]
WHERE CostRate = 7.77;
DECLARE @SQL nvarchar(4000);
-- Change as necessary
SET @SQL = 'SELECT DISTINCT [Name], 7.77 FROM [AdventureWorks].[Person].[CountryRegion];'
EXEC [dbo].[usp_TEST_TVP] @SQL;
GO
-- AFTER - should return values (238 rows on my system).
SELECT *
FROM [AdventureWorks].[Production].[Location]
WHERE CostRate = 7.77;