CODEWITHSUNDEEP
sql-server-2008
bob sponge
bob sponge

Reputation: 3

Is there a way to set multiple values in one variable in SQL Server 2008?

I am a newbie in coding.

Here is what I did:

DECALRE @v VARCHAR(100)

SET @v = (SELECT TOP 100 NAMES FROM TestTable WITH(NOLOCK))

SELECT @v AS SampleData

But it returned an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Are there any way to set multiple values in one variable in SQL Server 2008?

Thanks in advance.

Upvotes: 0

Views: 44

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

There is one type of variable designed for holding multiple values. It's called a table variable:

declare @v table (Name varchar(100) not null)

insert into @v(Name )
select top 100 name from TestTable /* no ORDER BY means this is ill-defined */

You can insert/update/delete in this table variable and query from it via selects in exactly the same way as any other table.

Note though that this looks like you're breaking things down into "procedural" steps - first I'll get the top 100 names, then I'll do X, then I'll do Y. In SQL, you should try to put as much as possible into single queries, and leave it to the optimizer to determine what order to do things in, which subresults should be stored, etc.

Upvotes: 2

Related Questions