How to Insert select result into Variable which returns more than one row

Question

How to Insert select result into Variable which returns more than one row

Sangeeth Raj

Hall of Fame

Points: 3541
More actions
July 8, 2016 at 3:06 am

#400379

Hi,
I am trying to load select output result into variable, but I am not able to achieve either using SET or Select method.
Eg @
declare @v-2 varchar(max) = ''
select @v-2 = (select productname from product.products)
Error Msg :Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
set @v-2 = (select productname from product.products)
Error Msg :Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The select query is bit complex, though it is resulting one column with 35rows.
I believe the above error message you may come across in your early days, could some one provide some suggestions on it.
Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Sangeeth878787 (7/8/2016)
Hi,
I am trying to load select output result into variable, but I am not able to achieve either using SET or Select method.
Eg @
declare @v-2 varchar(max) = ''
select @v-2 = (select productname from product.products)
Error Msg :Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
set @v-2 = (select productname from product.products)
Error Msg :Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The select query is bit complex, though it is resulting one column with 35rows.
I believe the above error message you may come across in your early days, could some one provide some suggestions on it.
Thank you

Your variable is scalar - which value, from the rowset returned by your query, would you expect it to capture?

Alternatively, use a table variable.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

durga.palepu Hall of Fame Points: 3530 More actions · Answer 2

I think you would like to save the complete result set i.e. 35 rows into variable.

follow this:

DECLARE @tblV Table

(

v varchar(max)

)

INSERT INTO @tblV(v)

SELECT ProductName

FROM tblProducts

Alternatively, if you would like to store entire 35 records into single variable use:

SELECT STUFF((SELECT ', ' + CAST(ProductName AS VARCHAR(100)) [text()]

FROM tblProducts

FOR XML PATH(''), TYPE)

.value('.','NVARCHAR(MAX)'),1,2,' ') List_Output

the above code might have little syntax errors but will work.