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
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
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
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;