July 31, 2008 at 7:32 am
I'm getting an error of 'subquery returned more than 1 value' when I try to
run this, which I guess is because the @Approved is being set to a SELECT
that returns multiple rows. How do I go about this so that I can accomplish
create proc
as
begin
Declare @Approve int,
@Approved DateTime
set @Approve = (select convert(int,SettingValue)
from dbo.Setting
where SettingCode = 'SDV')
set @Approved = (select dateadd(dd,@Approve,ReviewedDate)
from dbo.Item)
if @Approved >= getdate()
begin
update Item
set ModelYN = 'True'
where dateadd(dd,@Approve,ReviewedDate) >= getdate()
end
end
July 31, 2008 at 7:42 am
How about making this one change:
SELECT @Approve = convert(int,SettingValue)
from dbo.Setting
where SettingCode = 'SDV'
July 31, 2008 at 7:43 am
LeeLuv (7/31/2008)
I'm getting an error of 'subquery returned more than 1 value' when I try torun this, which I guess is because the @Approved is being set to a SELECT
that returns multiple rows. How do I go about this so that I can accomplish
in your subqueries, use something like a TOP 1/ORDER BY or a WHERE clause to guarantee that you only get 1 row back.
July 31, 2008 at 7:43 am
luv,
you can use this:
set @Approve = (select top 1 convert(int,SettingValue)
from dbo.Setting
where SettingCode = 'SDV')
set @Approved = (select top 1 dateadd(dd,@Approve,ReviewedDate)
from dbo.Item)
this query will work as per your requirements but check its matching to your result or not..i am not sure with respect to result...
Cheers!
Sandy.
--
July 31, 2008 at 7:52 am
Hi All
but i need to update all the rows where @Approved is >= getdate() if i use TOP1 will tht work?
July 31, 2008 at 7:55 am
if you use an ORDER BY along with the TOP 1 to get the lowest date in your range, then yes, it should.
July 31, 2008 at 8:25 am
thanx it worked u guys are stars:D
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply