469,579 Members | 1,899 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

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

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?
Jul 12 '07 #1
2 1284
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.
Jul 12 '07 #2
Infide
28
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?
In my world, this is better practice:
SELECT @SavedSearches = count(id), username
from endeavor_memberinfo
where something = something

Because this allows you to do this:

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


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

Instead try:

SELECT count(id) as countr, username FROM endeavor_search WHERE username IN (SELECT username FROM endeavor_memberInfo WHERE workingWith = @workingWithID) AND searchDate > @maxDate GROUP BY username;
Jul 12 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by MX1 | last post: by
4 posts views Thread by Doris | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.