October 30, 2007 at 1:37 am
Hello everyone, i m using SQLServer2000 here is the table.
Table fields:
WantedItemID int (PK)
MemberID int (FK)
LotID (FK)
TotalListingCost numeric
Quantity numeric
OfferPrice numeric
OfferPriceUnit int
in my webpage, i allowed the users to update the Quantity and OfferPrice, so whenever quantity or offerPrice is updated, i want to
check if the OfferPriceUnit is 1 then multiply Quantity with OfferPrice for all rows in tha table but where MermebID,LotID and OfferPrice is not null and make sum in the variable after finishing mean deal with all rows in the table, i want to update my table and set the TotalListingCost with tha valuse of that variable and if the OfferPriceUnit is 2 the simply add the offerprices for all rows (where MemberID=@MemberID,LotID=@LotID and Offerprice is not null)and at the end of all rows
update the TotalListingCost in the table, i will try to make this check it out my query as i dont know in the loop how to deal as it multiply price with quantiti if type=1 in thoese rows where MemberID=@MemberID,LotID=@LotID and Offerprice is not null .
Query is:
Create StoredProcedure test
@MemberID int,
@LotID int
As
declare @rowNo int
declare @i int
select @rowNo=count(*) from Offers
set @i=1
while ( @i <= @rowNo)
begin --loop begin
declare myvar numeric(13)
if(OfferPriceUnit==1)
begin
set myvar+= Quantity*OfferPrice
end
else
begin
myvar+= OfferPrice
end
End --loop end
Update OffersWantedItem
set TotalListingCost=myvar
where MemberID= @MemberID AND LotID = @LotID and OfferPrice !=null
end
plz reply me.
Thanx in Advance.
October 30, 2007 at 3:00 am
isa (10/30/2007)
...declare @rowNo int
declare @i int
select @rowNo=count(*) from Offers
set @i=1
while ( @i <= @rowNo)
begin --loop begin
declare myvar numeric(13)
if(OfferPriceUnit==1)
begin
set myvar+= Quantity*OfferPrice
end
else
begin
myvar+= OfferPrice
end
End --loop end
-- To calculate myvar:
SELECT SUM(CASE OfferPriceUnit
WHEN 1 THEN Quantity * OfferPrice
ELSE OfferPrice
END)
FROM Offers
WHERE the condition about the user, ...
Databases and SQL are working with ordered/unordered sets/bags. In the above case there is no need to use a loop. If you would like to take advantage of the power of your database management system it may help if you read a book on basic database queries.
Update OffersWantedItem
set TotalListingCost=myvar
where MemberID= @MemberID AND LotID = @LotID and OfferPrice !=null
end
Also, do not use "OfferPrice !=null" (If ANSI NULLS are set, then this does not return either true or false, you get UNKNOWN instead! Have a look at http://msdn2.microsoft.com/en-us/library/aa196339(sql.80).aspx
Use "OfferPrice IS NOT NULL" instead.
Regards,
Andras
October 30, 2007 at 4:14 am
Thankyou so much for replying , thanx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply