How to strore multiple rows in a variable in sql server08

Question

Hi All, I want to know, How can I store multiple rows in a variable.i.e. My query will return multiple rows and i want to store it in a variable. Thanks in adv.

Answer 1

You can use a table valued parameter or you can use the old stand-by temp tables.

Answer 2

In SQL Server 2008, there's a feature called Table Valued Parameters which allow you to store rows into a variable that can be passed across stored procedures.

If however, your intent is to manipulate the data inside the same store procedure, then common table variables would be fine. Here's an Example:

DECLARE @MyTableVariable TABLE
(
  Id int,
  FirstName varchar(10),
  LastName varchar(10)
);

INSERT @MyTableVariable(Id, FirstName, LastName)
SELECT Id, FirstName, LastName FROM SomeOtherTable WHERE SomeCriteria

@MyTableVariable now contains all the rows matching the criteria, you can still continue to add more rows, do further manipulations and even return its contents.