CODEWITHSUNDEEP
sql-servert-sql
GhostDZ9
GhostDZ9

Reputation: 155

Query Error:Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

I have a variable 

DECLARE @CustomerName NVARCHAR(50);

I would like to take the results of the query below and concat it into the variable that I can use to return the values

SELECT Firstname, Lastname FROM CustomerData

100 Returned Records

The following query gives me the error in the subject

  DECLARE @CustomerName nvarchar(50);
  SELECT @CustomerName = (SELECT Firstname, Lastname FROM CustomerData)
  SELECT @CustomerName AS 'Customer Name';

How do I fix this?

Upvotes: 0

Views: 547

Answers (3)

John Ruddell
John Ruddell

Reputation: 25862

you need to have a colon before the equals to do assignment to your user defined variable

MySQL

SET @CustomerName := (SELECT GROUP_CONCAT(CONCAT(Firstname, ' ', Lastname)) FROM CustomerData);
SELECT @CustomerName AS 'Customer Name';

concat the names together because you can only assign one value to the variable

you can use SELECT INTO if you prefer it that way.

SELECT GROUP_CONCAT(CONCAT(Firstname, ' ', Lastname)) INTO @CustomerName FROM CustomerData;

T-SQL

DECLARE @CustomerName nvarchar(50);
SELECT @CustomerName = Firstname + ' ' + Lastname FROM customerdata;
SELECT @CustomerName;

this assumes you have only one name if you have more you should either add top 1 (as Lawrence did in his answer) or you can stuff it all in one string like so.

SELECT @CustomerName = (
  SELECT 
    stuff(
        (
        select cast(',' as varchar(max)) + c.firstname + ' ' + c.lastname
        from customerdata c

        order by c.firstname
        for xml path('')
        ), 1, 1, ''
    ) AS Customers
);

Upvotes: 0

Laurence
Laurence

Reputation: 10976

A scalar variable can only be bound to a single value. For example:

declare @CustomerName nvarchar(50);
select top 1 @CustomerName = Firstname + ' ' + Lastname from CustomerData;

If you have more than one row, a scalar variable probably won't do what you want.

Example SQL Fiddle

Upvotes: 1

narendra
narendra

Reputation: 1278

Use concat function to join the firstname and lastname as below...

-- for mysql
SELECT @CustomerName = (SELECT concat(Firstname,' ',Lastname) AS Name FROM CustomerData)

-- for sql-server
SELECT @CustomerName = (SELECT Firstname + ' ' + Lastname AS Name FROM CustomerData)

Upvotes: 0

Related Questions