SQL query error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." Output parameter

I have a SQL query that is being used in a C# controller class to return search query results:

SELECT @Output = (SELECT Name, Id
                  FROM [dbo].[Users]    
                  WHERE Name = 'Robert');

However, when I run this query, I get the following error:

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

I know that simply removing one of the column names will fix the issue, but I want to be able to return the Name and ID for each searched user.

Is there a way to change my query so that I can return the desired columns and still keep my @Output parameter in the query? Any recommendations would be much appreciated

2 answers

  • answered 2019-01-11 05:15 Avi

    Not sure if this is what you were looking for, you can declare @output as table, and insert the values when condition is true.

      Declare @output table ([name] varchar(50), ID int) 
    
      insert into @output([name], id)     select 'abc', 1 
    
      select * from @output 
    

    Output: This is just sample data

     name   ID
     abc    1
    

  • answered 2019-01-11 12:24 Gordon Linoff

    One method is to concatenate them:

    SELECT @Output = (SELECT Name + '|' + CONVERT(VARCHAR(255), Id)
                      FROM [dbo].[Users]    
                      WHERE Name = 'Robert'
                     );
    

    Another alternative is to use two variables, because you want two items:

    SELECT @Name = Name, @Id = Id
    FROM [dbo].[Users]    
    WHERE Name = 'Robert';
    

    Given that you know that the name is 'Robert', I don't see why that is useful. You can do:

    DECLARE @Name VARCHAR(255);
    
    SET @Name = 'Robert';
    
    SELECT @Id = Id
    FROM [dbo].[Users]    
    WHERE Name = @Name;