April 4, 2007 at 3:39 am
CREATE FUNCTION GetItemSoldCount
(
@ItemID int
)
RETURNS int
AS
BEGIN
DECLARE @TotalCount int
SELECT @TotalCount = sum(Quantity) from ResellerItems
where ItemID= @ItemID and ItemStatusID=1 or ItemStatusID=2
RETURN (@TotalCount)
END
===================================================================
CREATE FUNCTION GetItemQuantityAvaliable
(
@ItemQuantity int,
@ItemsSold int -- At here i want to get the result of the First Function e.g @TotalCount , pass paramter which is the result of First function.
)
RETURNS int
AS
BEGIN
DECLARE @TotalAvaliable int
Set @TotalAvaliable=@ItemQuantity-@ItemsSold
RETURN (@TotalAvaliable)
END
==================================================================
CREATE PROCEDURE GetActiveListings
@DistributorID [int]
AS
SELECT Item.ItemID,Item.ItemTitle, Item.Quantity,Item.Duration,Item.DistributorPrice, Item.ResellerPrice,dbo.IfImageExists(defaultImageURL) as defaultImageURL,
count( ResellerItems.ResellerID) AS ItemResellerCount,dbo.GetItemSoldCount(Item.ItemID) as QuantitySold,Item.Quantity-dbo.GetItemSoldCount(Item.ItemID) as QuantityAvaliabl
FROM Item INNER JOIN
ResellerItems ON Item.ItemID = ResellerItems.ItemID INNER JOIN
Resellers ON ResellerItems.ResellerID = Resellers.ResellerID
where Item.DistributorID=@DistributorID and Item.ItemStatusID=1
Group By Item.ItemID,Item.ItemTitle,Item.Quantity, Item.Duration,Item.DistributorPrice, Item.ResellerPrice,defaultImageURL
GO
===========================================================================
Hello everyone , i m using Sql server 2000, check the functions and SP ,now in my Stored Procedure in the select statement i want to call both functions first
gives the sum of the Quantity sold and the second gives the output of Items available(that was getting from the Item.Quantity - Result of the First function) as
in the Select Query, see its not called the 2nd function coz when i called the 2nd Function in my select statement:
select ......., dbo.GetItemQuantityAvaliable(Item.Quantity,Result of First function) (Result of 1st function is the int value)
plz help me how i do this?
Thanx in Advance.
April 4, 2007 at 5:11 am
Why are you using functions to do simple calculations? Or is there more complex query that you have not given?
You have a logic error in GetItemSoldCount. Whenever you use AND and OR in the same WHERE clause you must use brackets to clearly define the logic.
WHERE ItemID = @ItemID AND ItemStatusID=1 OR ItemStatusID=2
is the same as
WHERE (ItemID = @ItemID AND ItemStatusID=1) OR ItemStatusID=2
ie it will select any ItemStatusID=2 regardless of ItemID, it should be
WHERE ItemID = @ItemID AND (ItemStatusID=1 OR ItemStatusID=2)
unless you really wanted all ItemStatusID=2, in which case you still uses brackets
You can use both functions together in your query, ie
dbo.GetItemQuantityAvaliable(Item.Quantity,dbo.GetItemSoldCount(Item.ItemID)) AS [QuantityAvaliable]
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply