Stored procedure in SELECT

[Bluelove]

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'.

 

[collierd]

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(...)

 

[Bluelove]

the sp_productprice already returns an output @Price of type "money"

 

[collierd]

whats the code behind sp_productprice ?

 

[Bluelove]

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

 

[collierd]

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

Now just add sp_ProductPrice to your select (replace exec ...) and it should work

Damian.

 

[Bluelove]

Great
appreciate your help
it works now

Thanks