Just how awesome are table parameters in SQL Server 2008?

I would have to say, that one of the coolest new features of SQL Server 2008 is the ability to pass a table as a single parameter to a stored procedure.

While we have been able to do this in the past, by using XML to pass more than one value in, then break it apart. But this is just such a simpler, easier, more elegant solution.

It is a bit of a process to get it done, but once it is all setup it is a piece of cake to use.

You can’t just create a table as part of the input parameter to the stored procedure like this.

CREATE PROCEDURE YourProcedure
     @YourTable TABLE (Col1 INT)
AS
...
GO

That would be to easy. First you have to create a User Defined Table Type by using the CREATE TYPE command. Then you create an input (or output) parameter using this table type then in your calling code create a parameter using this same user defined table type and load it with data, then call the procedure just as you normally would.

CREATE TYPE MyTableType AS TABLE
   (Id INT)
GO
CREATE PROCEDURE MyProcedure
   @Ids MyTableType OUTPUT
AS
INSERT INTO @Ids
SELECT object_Id
FROM sys.objects
GO
DECLARE @values MyTableType
exec MyProcedure @Ids=@values OUTPUT
SELECT *
FROM @values
GO
DROP PROCEDURE MyProcedure
GO
DROP Type MyTableType
GO

Personally I can’t wait to to begin using this new feature, but it’ll probably be a while before we convert our system to require SQL Server 2008, as we have customers who are still running SQL Server 2000 and aren’t happy about our requirement for SQL Server 2005.

Denny

Share

2 Responses

  1. The given code doesn’t work.
    It raises this error:

    Server: Msg 352, Level 15, State 1, Line 1
    The table-valued parameter <Parameter Name> must be
    declared with the READONLY option.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?