Just how awesome are table parameters in SQL Server 2008?

Published On: 2008-12-08By:

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


Contact the Author | Contact DCAC

One response to “Just how awesome are table parameters in SQL Server 2008?”

  1. ninjacross says:

    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.

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via