CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Ger Mc
Ger Mc

Reputation: 640

Using WHERE with stored procedures

I'm getting a syntax error for the WHERE clause in the following code. Whats the problem?

CREATE PROC spIncreaseHours
(@ID int, @HoursPerWeek DEC, @HourlyRate INT)
AS
INSERT INTO Payments
Values (@HoursPerWeek, @HourlyRate)
WHERE ID = @ID;

Upvotes: 0

Views: 40

Answers (2)

DavidG
DavidG

Reputation: 118937

You cannot use WHERE with an INSERT. You either want to be inserting a new row, or updating an existing one. For example INSERT:

CREATE PROC spIncreaseHours
(@ID int, @HoursPerWeek DEC, @HourlyRate INT)
AS
INSERT INTO Payments
Values (@ID, @HoursPerWeek, @HourlyRate);

Or UPDATE:

CREATE PROC spIncreaseHours
(@ID int, @HoursPerWeek DEC, @HourlyRate INT)
AS
UPDATE Payments
SET HoursPerWeek = @HoursPerWeek, 
     HourlyRate = @HourlyRate
WHERE ID = @ID;

Of course, there is also a third possibility where you don't know if a row for @ID already exists, so you can do a check first:

CREATE PROC spIncreaseHours
(@ID int, @HoursPerWeek DEC, @HourlyRate INT)
AS
IF EXISTS (SELECT ID FROM Payments WHERE ID = @ID)
    UPDATE Payments
    SET HoursPerWeek = @HoursPerWeek, 
        HourlyRate = @HourlyRate
    WHERE ID = @ID;
ELSE
    INSERT INTO Payments
    Values (@ID, @HoursPerWeek, @HourlyRate);

Upvotes: 1

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

You probably want to UPDATE:

CREATE PROC spIncreaseHours
(@ID int, @HoursPerWeek DEC, @HourlyRate INT)
AS
UPDATE Payments set 
HoursPerWeek = @HoursPerWeek, 
HourlyRate = @HourlyRate
WHERE ID = @ID;

Upvotes: 3

Related Questions