SP kills a query when put in a parameter... help?

[trickyidiot]

This query executes fine by itself:
SELECT count(id) as countr, username FROM endeavor_search WHERE IN (SELECT username FROM endeavor_member Info 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_member Info WHERE workingWith = @workingWithID) AND searchDate > @maxDate GROUP BY username);

Error:
Server: Msg 116, Level 16, State 1, Procedure endeavor_sp_mem berRecentActivi ty, 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?

[SkinHead]

You're selecting two variables :

SELECT count(id) as countr, username

...Of course you can't assign the result to a single variable !!!!

Also, It looks very likely that you would get multiple records as a result of the query. Again....Of course you can't assign the result to a single variable !!!!

It's not the parameter. It's the query.

[Infide]

This query executes fine by itself:
SELECT count(id) as countr, username FROM endeavor_search WHERE IN (SELECT username FROM endeavor_member Info 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_member Info WHERE workingWith = @workingWithID) AND searchDate > @maxDate GROUP BY username);

Error:
Server: Msg 116, Level 16, State 1, Procedure endeavor_sp_mem berRecentActivi ty, 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?

In my world, this is better practice:
SELECT @SavedSearches = count(id), username
from endeavor_member info
where something = something

Because this allows you to do this:

SELECT @SavedSearches = count(id),
@username = username
from endeavor_member info
where something = something


"SELECT count(id) as countr, username FROM endeavor_search WHERE IN (SELECT username FROM endeavor_member Info WHERE workingWith = @workingWithID) AND searchDate > @maxDate GROUP BY username;"

Instead try:

SELECT count(id) as countr, username FROM endeavor_search WHERE username IN (SELECT username FROM endeavor_member Info WHERE workingWith = @workingWithID) AND searchDate > @maxDate GROUP BY username;