SQL’s Table Valued Custom Types

SQL Server allows you to pass complex types to Stored Procedures and User Functions as parameters. In the past, this was overcome by using CSV strings and splitting them. Now, with complex types (think, tables) we can pass whatever we need and return whatever we need back. There’s a limitation that as of 2008 you can only pass READONLY variables, but this is easily overcome by re-inserting into memory tables if the need to edit is there. Here’s the syntax to do it – just paste into SQL Management Studio to see:

Here’s the raw code: http://codepaste.net/tieqkw
 
SET NOCOUNT ON
/*
    by: jerry
    on: 9/2011
    to: demo table types
*/

-- Create the data type
CREATE TYPE MyTableType AS TABLE
(
    Key1 int,
    Key2 int
)
GO

-- Create the proc receiving param
CREATE PROC up_MySlaveProc
    @table MyTableType READONLY
AS
    PRINT 'HELLO FROM SLAVE'
    select * from @table
GO

-- Create the proc sending param
CREATE PROC up_MyMasterProc
    /* no parameters */
AS
    PRINT 'HELLO FROM MASTER'
    declare @table MyTableType
    insert into @table values
         (1, 2)
        ,(2, 2)
        ,(3, 2)
    exec up_MySlaveProc @table = @table
GO

-- Call the sample procs
exec up_MyMasterProc;