Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored procedure in SELECT

Status
Not open for further replies.

Bluelove

Programmer
Oct 19, 2002
9
BE
Hi, i have this problem

in a stored procedure i have a SELECT statement, and in this SELECT statement another stored procedure should be executed

Is this possible? Cause now i have this and i get 2 errors

CREATE PROCEDURE sp_1
......
......
INSERT INTO OrderItem (OrderID, ProductID, Quantity, Price, Promo)
SELECT @OrderID, ShoppingCartItem.ProductID, ShoppingCartItem.Quantity,
EXEC sp_ProductPrice (ShoppingCartItem.ProductID, @CustomerID),
Product.Promo
FROM ShoppingCartItem
INNER JOIN Product ON ShoppingCartItem.ProductID = Product.ProductID
WHERE CustomerID = @CustomerID



Server: Msg 156, Level 15, State 1, Procedure sp_InsertOrder, Line 18
Incorrect syntax near the keyword 'EXEC'.
Server: Msg 170, Level 15, State 1, Procedure sp_InsertOrder, Line 18
Line 18: Incorrect syntax near 'ShoppingCartItem'.
 
Try creating sp_ProductPrice as a function which uses the 2 variables and returns a variable of the required data type
(i.e. same as Price)

You just need the function to do the equivalent of the procedure

That way it can be called without the exec which is used incorrectly in this instance

It might need calling as dbo.sp_ProductPrice(...)
 
the sp_productprice already returns an output @Price of type "money"
 
It's very complex, and i didn't wrote any comments yet, but a product has 6 prices, 3 normal and 3 promo prices, according to the promostatus and the customer his status, the correct price should be displayed, so that's what it is all about

and for every product (above) i need the price, to put in the OrderItem colomn, that's why i need to execute this stored procedure in that other, wich i don't knwo how to, cause i get these errors



CREATE PROCEDURE sp_ProductPrice (@ProductID int, @CustomerID int, @Price money OUTPUT) AS

DECLARE @Promo bit, @Cat int
SET @Promo = (SELECT Promo FROM Product WHERE ProductID = @ProductID)
IF @CustomerID = 0
BEGIN
IF @Promo = 1

SELECT @Price = PromoPrice1 FROM Product WHERE ProductID = @ProductID

ELSE

SELECT @Price = Price1 FROM Product WHERE ProductID = @ProductID
END
ELSE
BEGIN
SET @Cat = (SELECT PriceCat FROM Customer WHERE CustomerID = @CustomerID)

IF @Promo = 1

SELECT @Price = (CASE @Cat WHEN 1 THEN PromoPrice1 WHEN 2 THEN PromoPrice2 WHEN 3 THEN PromoPrice3 END)
FROM Product WHERE ProductID = @ProductID

ELSE

SELECT @Price = (CASE @Cat WHEN 1 THEN Price1 WHEN 2 THEN Price2 WHEN 3 THEN Price3 END)
FROM Product WHERE ProductID = @ProductID
END


GO
 
Stored procedures and functions are more or less the same except that a function can be used in the select statement, i.e. to use exec ... is invalid

Based on that and looking at the code I would write a function as (something like):

[/code]
Create Function sp_ProductPrice (@ProductID int, @CustomerID int)

returns money

as
begin
DECLARE @Promo bit, @Cat int
SET @Promo = (SELECT Promo FROM Product WHERE ProductID = @ProductID)
IF @CustomerID = 0
BEGIN
IF @Promo = 1

SELECT @Price = PromoPrice1 FROM Product WHERE ProductID = @ProductID

ELSE

SELECT @Price = Price1 FROM Product WHERE ProductID = @ProductID
END
ELSE
BEGIN
SET @Cat = (SELECT PriceCat FROM Customer WHERE CustomerID = @CustomerID)

IF @Promo = 1

SELECT @Price = (CASE @Cat WHEN 1 THEN PromoPrice1 WHEN 2 THEN PromoPrice2 WHEN 3 THEN PromoPrice3 END)
FROM Product WHERE ProductID = @ProductID

ELSE

SELECT @Price = (CASE @Cat WHEN 1 THEN Price1 WHEN 2 THEN Price2 WHEN 3 THEN Price3 END)
FROM Product WHERE ProductID = @ProductID
END


return @Price
end
Code:
Now just add sp_ProductPrice to your select (replace exec ...) and it should work

Damian.
 
Great
appreciate your help
it works now :)

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top