quote:

---

Originally posted by tazzy0429

I have to create a database for car sales with a stored procedure that uses a bridge table to use cost, sales price and commision to calculate the profit. i keep getting these errors :

Msg 102, Level 15, State 1, Procedure GetProfit, Line 9
Incorrect syntax near '@Sale_Price'.
Msg 137, Level 15, State 2, Procedure GetProfit, Line 20
Must declare the scalar variable "@Cost".

Can anyone tell me what I am doing wrong I have stared at this until it has had me going in circles.


CREATE PROCEDURE GetProfit
-- Add the parameters for the stored procedure here
@Cost numeric = 0,
@Sale_Price numeric = 0 ,
@Commision_Rate numeric = 0,
@Vin_Number varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Update Customer_vehicle_Sales_bridge
set Profit = (@Cost - @Sale_Price - (@Sale_Price * @Commission_Rate))
where Customer_vehicle_Sales_bridge.Vin_Number = @Vin_Number


END
GO

---

Update Customer_vehicle_Sales_bridge
set Profit = (@Cost - @Sale_Price - (@Sale_Price * @Commission_Rate))
where Vin_Number = @Vin_Number

quote:

---

Originally posted by tazzy0429


Update Customer_vehicle_Sales_bridge
set Profit = (@Cost - @Sale_Price - (@Sale_Price * @Commision_Rate))
where Vin_Number = @Vin_Number

if I take the from statement out it does this::

Msg 2714, Level 16, State 3, Procedure GetProfit, Line 19
There is already an object named 'GetProfit' in the database.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

IF EXISTS(SELECT 1 FROM sys.sql_modules WHERE OBJECT_DEFINITION(object_id) = 'GetProfit')
 DROP PROCEDURE GetProfit
GO

CREATE PROCEDURE GetProfit 
-- Add the parameters for the stored procedure here
@Cost numeric = 0,
@Sale_Price numeric = 0 ,
@Commision_Rate numeric = 0,
@Vin_Number varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Update Customer_vehicle_Sales_bridge
set Profit = (@Cost - @Sale_Price - (@Sale_Price * @Commission_Rate))
where Vin_Number = @Vin_Number


END
GO

IF EXISTS(SELECT 1 FROM sys.sql_modules WHERE OBJECT_DEFINITION OBJECT_NAME(object_id) = 'GetProfit')
 DROP PROCEDURE GetProfit
GO

CREATE PROCEDURE GetProfit 
-- Add the parameters for the stored procedure here
@Cost numeric = 0,
@Sale_Price numeric = 0 ,
@Commision_Rate numeric = 0,
@Vin_Number varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Update Customer_vehicle_Sales_bridge
set Profit = (@Cost - @Sale_Price - (@Sale_Price * @Commission_Rate))
where Vin_Number = @Vin_Number


END
GO