using *tables* as Table-Valued Parameters (TVP)

MS SQL 2008 supports TVP: a useful feature for bulk uploading data to a stored proceedure for processing.

Rather than create a user-defined type, is it possible to leverage an existing table definition? For example, is it possible to create a stored proceedure with the following signature?

CREATE PROCEDURE usp_InsertProductionLocation
@TVP **LocationTable** READONLY

The documentation seems to suggest that this is not possible.

SAMPLE CODE

/*
Sample code from:
http://msdn.microsoft.com/en-us/library/bb510489.aspx
*/

USE AdventureWorks2008R2;
GO

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE 
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2008R2].[Production].[Location]
           ([Name]
           ,[CostRate]
           ,[Availability]
           ,[ModifiedDate])
        SELECT *, 0, GETDATE()
        FROM  @TVP;
        GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP 
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM 
    [AdventureWorks2008R2].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

/*
The following is not part of the original source code:
*/

CREATE TABLE LocationTable(
 LocationName VARCHAR(50)
, CostRate INT );
GO

No, you can't leverage an existing table definition, you need to define an explicit type. This was asked for back in 2007 and was closed as "won't fix" but I still strongly encourage you to up-vote and leave a comment describing your use case and how this will help your business be more productive. You could even point to this question to demonstrate how tedious it can be to try and automate this.

• Declare a variable...(Formerly UserVoice #32891356 (Formerly Connect #294130))

You can do this today, dynamically, though... for example for your simple definition:

-- you would pass these two in as parameters of course:
DECLARE
  @TableName SYSNAME = N'LocationTable',
  @TypeName  SYSNAME = N'LocationTypeTable';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N',' + CHAR(13) + CHAR(10) + CHAR(9) 
    + QUOTENAME(c.name) + ' '
    + s.name + CASE WHEN LOWER(s.name) LIKE '%char' THEN 
        '(' + CONVERT(VARCHAR(12), (c.max_length/
        (CASE LOWER(LEFT(s.name, 1)) WHEN N'n' THEN 2 ELSE 1 END))) + ')' 
        ELSE '' END
        -- need much more conditionals here for other data types
    FROM sys.columns AS c
    INNER JOIN sys.types AS s
    ON c.system_type_id = s.system_type_id
    AND c.user_type_id = s.user_type_id
    WHERE c.[object_id] = OBJECT_ID(@TableName);

SELECT @sql = N'CREATE TYPE ' + @TypeName
    + ' AS TABLE ' + CHAR(13) + CHAR(10) + '(' + STUFF(@sql, 1, 1, '')
    + CHAR(13) + CHAR(10) + ');';

PRINT @sql;
-- EXEC sp_executesql @sql;

Results:

CREATE TYPE LocationTypeTable AS TABLE 
(
    [LocationName] varchar(50),
    [CostRate] int
);

Disclaimer: This does not deal with all kinds of other things like MAX types, precision and scale for numerics, etc. The final solution would have to be more robust to account for all potential column definitions but this should give you a start.

In SQL Server 2012 there are new DMVs and stored procedures that will make it much easier to derive column metadata from existing tables (or stored procedures or even ad hoc queries) without having to mess with all the conditional logic against sys.types and sys.columns. I blogged briefly about these enhancements in December. It's still tedious, but it's somewhere between the awful unmaintainable spaghetti above and the ability to just say " as copy of [table x]"...

StackExchange Database Administrators Q#12596, answer score: 19