SQL Server with Mr. Denny

Dec 8 2008   1:06PM GMT

Just how awesome are table parameters in SQL Server 2008?



Posted by: Denny Cherry
Tags:
CREATE PROCEDURE
SQL Server 2008
T/SQL
Table Variables

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

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • NinjaCross
    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.

    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: