This query executes fine by itself:
SELECT count(id) as countr, username FROM endeavor_search WHERE IN (SELECT username FROM endeavor_memberInfo WHERE workingWith = @workingWithID) AND searchDate > @maxDate GROUP BY username;
When trying to put it in a variable within a stored procedure, it fails:
SET @savedSearches = (SELECT count(id) as countr, username FROM endeavor_search WHERE EXISTS (SELECT username FROM endeavor_memberInfo WHERE workingWith = @workingWithID) AND searchDate > @maxDate GROUP BY username);
Error:
Server: Msg 116, Level 16, State 1, Procedure endeavor_sp_memberRecentActivity, Line 15
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
How to I fix this?