SQL error subquery returned more than 1 value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

SQL error subquery returned more than 1 value?

Author

Topic

chinni2383
Starting Member

1 Post

Posted - 2009-02-03 : 12:29:23

Hi,

When I execute the below stored procedure from C++ program I'm getting this error

Command Failed:
[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Installation Error! <[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.>

This statement produced errors:
EXEC dt_updateHistory

CREATE PROCEDURE dt_updateHistory
with ENCRYPTION
as

declare @dbversion float

if exists ( select * from sysobjects where name = 'DBVersion' )

begin
select @dbversion = ( select DBVersion from DBVersion )
if @dbversion < 2
begin
update AValue set Value= 2 where Value= 0
update AOutput set Value= 2 where Value= 0

end
end

GO
DBversion is a column name in the DBversion table. It will return only one value i.e 1. But I'm not sure why i'm getting this error.
Please help me. This is blocking my progress.

Thanks,
Chinni

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 12:39:25

run this and see how many rows it return

select DBVersion from DBVersion

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-02-03 : 12:46:09

You have duplicate rows for dbversion which is why you get the error.

Try This..


CREATE PROCEDURE dt_updateHistory
with ENCRYPTION
as

if exists ( select * from sysobjects where name = 'DBVersion' )
declare @dbversion float
begin
set @dbversion=(select distinct @dbversion from dbverion where dbVersion = 0)  --or some other number < 2

IF (@dbversion < 2)
BEGIN
UPDATE AValue 
   SET Value=2
 WHERE Value=0

UPDATE AOutput 
   SET Value= 2
 WHERE Value=0
END
ELSE
END --dbversion isn't less than 2 so just exit..

GO

Something like that maybe?

r&r

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 13:06:45

quote:

Originally posted by revdnrdy

You have duplicate rows for dbversion which is why you get the error.

Try This..


CREATE PROCEDURE dt_updateHistory
with ENCRYPTION
as

if exists ( select * from sysobjects where name = 'DBVersion' )
declare @dbversion float
begin
set @dbversion=(select distinct @dbversion from dbverion where dbVersion = 0)  --or some other number < 2

IF (@dbversion < 2)
BEGIN
UPDATE AValue 
   SET Value=2
 WHERE Value=0

UPDATE AOutput 
   SET Value= 2
 WHERE Value=0
END
ELSE
END --dbversion isn't less than 2 so just exit..

GO

Something like that maybe?

r&r

small typo. no need of @

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources