SP Select statement fails when put in variable...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP Select statement fails when put in variable...

Author

Topic

trickyidiot
Starting Member

7 Posts

Posted - 2007-07-12 : 15:43:31

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?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-12 : 15:48:27

you have to return only one column and not two like your doing
change
SELECT count(id) as countr, username FROM
to
SELECT count(id) as countr FROM

and i'm not even going to touch the subject of not writing sql like that...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

trickyidiot
Starting Member

7 Posts

Posted - 2007-07-12 : 15:51:10

"and i'm not even going to touch the subject of not writing sql like that..."

Please do - I'm always up for learning how to do my job better.

The problem with not selecting username along with the sum is that the results become useless to me.

Is there another way to achieve the results I'm looking for?

Thanks for the reply btw :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-12 : 16:03:27

you're in luck

jeff just posted about how not to write sql like that:
http://weblogs.sqlteam.com/jeffs/archive/2007/07/12/60254.aspx

and to store both values in the variables you have to use 2 varables:
declare @userName varchar(100)
SELECT @savedSearches = count(id), @userName = username FROM endeavor_search WHERE EXISTS (SELECT username FROM endeavor_memberInfo WHERE workingWith = @workingWithID) AND searchDate > @maxDate GROUP BY username

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

trickyidiot
Starting Member

7 Posts

Posted - 2007-07-12 : 16:25:34

THANK YOU
THANK YOU THANK YOU
THANK YOU THANK YOU THANK YOU

Your query works flawlessly.

I will engorge the page you linked several times over

trickyidiot
Starting Member

7 Posts

Posted - 2007-07-12 : 16:26:36

Now... storing the results with username right nest tot he corresponding sum...

This is how I understand it to work, but I wouldn't know how to add in the usernames..

-- Create Temporary Local Table for saved searches --
DECLARE @svdSrchTable TABLE (srchSum int(4), username varchar(50))
INSERT INTO @svdSrchTable SELECT value FROM dbo.fnc_split(@savedSearches, ',')

trickyidiot
Starting Member

7 Posts

Posted - 2007-07-12 : 16:30:29

Would this work?

-- Set SAVED SEARCHEs --
declare @userNameSS varchar(100)
SELECT @savedSearches = count(id), @userNameSS = username FROM endeavor_search WHERE EXISTS (SELECT username FROM endeavor_memberInfo WHERE workingWith = @workingWithID) AND searchDate > @maxDate GROUP BY username

-- Create Temporary Local Table for saved searches --
DECLARE @svdSrchTable TABLE (srchsum int, username varchar(50))
INSERT INTO @svdSrchTable (srchsum,username) VALUES(SELECT value FROM dbo.fnc_split(@savedSearches, ','),SELECT value FROM dbo.fnc_split(@usernameSS, ','))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources