Subquery returned more than 1 value.

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)

Subquery returned more than 1 value.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-11-02 : 07:49:14

Shane writes "I am needing to parse out the bit value from a column in a database separated as "active" (1) and "inactive" (0). I am running this in a Stored Procedure to be ran from a website page.

The original SP just pulled the data to allow pulling the data and sorting it by the field/column.

For one of these fields I now need the ability to show just active or just inactive.

I get the error message stated in the subject.

Any help you can give me would be appreciated.

::STORED PROCEDURE::

CREATE PROC SP_Get_User_Listing_alt( @strOrderBy varchar(20) )
AS
/*
NAME: SP_Get_User_Listing
PARAMS: none
*/
--Note: I still need to implement the roles... and other stuff
DECLARE curUserList CURSOR LOCAL READ_ONLY FOR
SELECT id, username, password, firstname, lastname, active FROM users
DECLARE @ReturnData TABLE ( id int,
username varchar(50),
password varchar(50),
firstname varchar(50),
lastname varchar(50),
active int,
inactive int,
fourweektotal money,
yeartodate money)
DECLARE @currentid int
DECLARE @username varchar(50)
DECLARE @password varchar(50)
DECLARE @firstname varchar(50)
DECLARE @lastname varchar(50)
DECLARE @active int
DECLARE @inactive int
DECLARE @total money
DECLARE @YTDtotal money
OPEN curUserList
FETCH NEXT FROM curUserList INTO @currentid, @username, @password, @firstname, @lastname, @active
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC SP_GetTotal_4week @currentid, @total OUTPUT
EXEC SP_GetTotal_YTD @currentid, @YTDtotal OUTPUT
SELECT @active = (SELECT active FROM users WHERE active = 1)
SELECT @inactive = (SELECT active FROM users WHERE active = 0 )
INSERT @ReturnData
SELECT @currentid, @username, @password, @firstname, @lastname, @active, @inactive, @total, @YTDtotal
FETCH NEXT FROM curUserList INTO @currentid, @username, @password, @firstname, @lastname, @active
END
CLOSE curUserList
DEALLOCATE curUserList
IF @strOrderBy = 'id'
SELECT * FROM @ReturnData ORDER BY id
ELSE IF @strOrderBy = 'username'
SELECT * FROM @ReturnData ORDER BY username
ELSE IF @strOrderBy = 'firstname'
SELECT * FROM @ReturnData ORDER BY firstname
ELSE IF @strOrderBy = 'lastname'
SELECT * FROM @ReturnData ORDER BY lastname
ELSE IF @strOrderBy = 'active'
SELECT * FROM @ReturnData ORDER BY active desc
ELSE IF @strOrderBy = 'inactive'
SELECT * FROM @ReturnData ORDER BY inactive desc
ELSE IF @strOrderBy = 'fourweektotal'
SELECT * FROM @ReturnData ORDER BY fourweektotal desc
ELSE IF @strOrderBy = 'yeartodate'
SELECT * FROM @ReturnData ORDER BY yeartodate desc
ELSE
SELECT * FROM @ReturnData ORDER BY id
RETURN
GO"

Kristen
Test

22859 Posts

Posted - 2006-11-02 : 07:56:07

SELECT @active = (SELECT active FROM users WHERE active = 1)
or
(SELECT active FROM users WHERE active = 0 )

is returning more than one row.

Should you be using EXISTS instead?

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-11-02 : 07:58:25

yuk what a mess.

SELECT @active = (SELECT active FROM users WHERE active = 1)

What is that trying to do?
It will only woirk if there is a single active user in which case it will set @active to 1.
Why not
select @active = 1
where exists (SELECT * FROM users WHERE active = 1)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-11-02 : 11:41:43

quote:
Originally posted by AskSQLTeam

Shane writes "I am needing to parse out the bit value from a column in a database separated as "active" (1) and "inactive" (0). I am running this in a Stored Procedure to be ran from a website page.

The original SP just pulled the data to allow pulling the data and sorting it by the field/column.

For one of these fields I now need the ability to show just active or just inactive.

I get the error message stated in the subject.

Any help you can give me would be appreciated.

::STORED PROCEDURE::

CREATE PROC SP_Get_User_Listing_alt( @strOrderBy varchar(20) )
AS
/*
NAME: SP_Get_User_Listing
PARAMS: none
*/
--Note: I still need to implement the roles... and other stuff
DECLARE curUserList CURSOR LOCAL READ_ONLY FOR
SELECT id, username, password, firstname, lastname, active FROM users
...

You say you need to show just active or just inactive users. How does the procedure know which of these you want?

If you add a @Active bit parameter, you could add:
DECLARE curUserList CURSOR LOCAL READ_ONLY FOR
SELECT id, username, password, firstname, lastname, active FROM users
WHERE active = @Active

and only return the ones requested. (and remove the SELECT @active = (SELECT ... code inside the loop)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources