How can I store more than one value in stored procedure?

Question

If I want to store more than one value in a variable how can I do that in stored procedure?

I mean we have array concept in normal programming do we have something like that in stored procedure

Do you mean something like [csv](http://en.wikipedia.org/wiki/Comma-separated_values)? — Martin Hennings, Mar 01 '11 at 10:58

score 2 · Accepted Answer · answered Mar 01 '11 at 10:58

2

If the storage is needed just within the scope of procedure then temp tables are there to rescue you.

answered Mar 01 '11 at 10:58

Furqan Hameedi

4,372
3
27
34

score 2 · Answer 2 · answered Mar 01 '11 at 11:00

Depending on your specific database, there are several options available.

In SQL Server, you can define

a temporary table for just your connection (CREATE TABLE #YourTable(.....))
a global temporary table visible to any connection (CREATE TABLE ##YourTable(.....))
a table variable (DECLARE @YourVariable TABLE (.....))

score 0 · Answer 3 · edited May 23 '17 at 11:55

You can create a variable that is a complete table. Here's a code sample:

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
OUTPUT inserted.BusinessEntityID,
       deleted.VacationHours,
       inserted.VacationHours,
       inserted.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

As you can see you declare it just like a normal table. It will go out of scope at the end of the procedure.

These are called table variables. There are also temporary tables which you can create, that work in much the same way, expect that you declare them with: create table #tmp (Col1 int, Col2 int);

There's a good SO post about the difference between the two here: What's the difference between a temp table and table variable in SQL Server?

And to go back to your original question: you can create a table variable and pretend it's an array (well it sort of is!). You just have to think in terms of SQL for array functions, so instead of .Find you would use a WHERE clause.

How can I store more than one value in stored procedure?

3 Answers3