help regarding case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help regarding case statement

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-28 : 06:56:14

Hi all,

I need help regarding a case statement. I am passing IsWholesaler .. a bit field, if it is 1 (i.e a user is wholesaler), then wholesaleprice will be selected, else if onsale bit is true then then saleprice will be selected from product table else regular price for the product will be selected.

I have created follownf sp but it is giving error on case statement

ALTER PROCEDURE [dbo].[CreateCustomerOrder]
(
--@CartID char(36),
@CustomerID int,
@CustomerName VARCHAR(101),
@ShippingRegion int,
@ShipmentType int,
@PaymentType int,
@CustomerEmail VARCHAR(100),
@OrderStatus int,
@ShippingCost float,
@Discount float,
@isWholesaler bit, /* it can be checked using select iswholesaler from users where userid = custid*/
@CreatedBy varchar(50)
)
AS
/* Insert a new record INTo Orders */
DECLARE @OrderID INT
--INSERT INTO Orders (CustomerID) VALUES (@CustomerID)INSERT INTO ORDERS(CustomerName,ShippingRegion,ShipmentType,PaymentType,CustomerEmail,CustomerID,OrderStatus,ShippingCost,Discount,IsActive,CreatedBy,CreatedOn)
VALUES(@CustomerName,@ShippingRegion,@ShipmentType,@PaymentType,@CustomerEmail,@CustomerID,@OrderStatus,@ShippingCost,@Discount,1,@CreatedBy,getdate())

/* Save the new Order ID */
SET @OrderID = scope_IDENTITY()
/* Add the order details to OrderDetail */
INSERT INTO OrderDetails (OrderID, ProductID, ProductName, Quantity, UnitCost)
SELECT @OrderID, p.ProductID, p.ProductName, s.Quantity,

case when @isWholesaler = 1 then UnitPrice_WholeSale
case when onsale= 0 then p.UnitPrice_retail
case when onsale= 1 then p.SalePrice untiprice
--P.UnitPrice_retail else p.SalePrice end RetailPrice,UnitPrice_WholeSale WholesalePrice,
-- p.UnitPrice_retail
FROM Products p JOIN ShoppingCart s
ON p.ProductID = s.ProductID
WHERE s.CreatedBy = @CustomerID
/* Clear the shopping cart */

DELETE FROM ShoppingCart
WHERE CreatedBy = @CustomerID
/* Return the Order ID */
SELECT @OrderID

Please help me on this.
Regards,
Asif Hameed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-28 : 07:41:03

[code]
case when @isWholesaler = 1 then UnitPrice_WholeSale
~~case~~ when onsale= 0 then p.UnitPrice_retail
~~case~~ when onsale= 1 then p.SalePrice
end[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-28 : 12:55:56

also last insert ...select is not correct. you have specified 5 columns in insert list (OrderID, ProductID, ProductName, Quantity, UnitCost) and you've given 6 fields in select. either add extra column in insert list or remove it from select. also remove last , after last column in select list.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources