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 varchar(max) = ''

  select @v = (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 = (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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 8, 2016 at 3:15 am

  #1889114

  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 varchar(max) = ''

  select @v = (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 = (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

  July 8, 2016 at 4:53 am

  #1889137

  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.