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
2 Responses
The given code doesn’t work.
It raises this error: