CODEWITHSUNDEEP
sqlsql-server
Michael Alan Nollette
Michael Alan Nollette

Reputation: 145

Trying to include both a select statement and user input in an insert statement

So I'm trying to create a small-scale payroll system as practice, utilizing WPF as an interface and connecting to a SQL Server database. I'm setting up the stored procedures, and I'm trying to arrange one to add a new work shift to the database. It takes user input for the employee's first and last name, then inserts a new shift, inputting the manually entered details, along with inserting an employee ID from the tblEmployee table where the employee's first and last name are the same as the user-input first and last name. Problem is that it's giving me an incorrect syntax error for the last line, and I can't seem to figure out why. 

CREATE PROCEDURE NewShift(
@LName varchar(50),
@FName varchar(50),
@Date date,
@ShiftStart time,
@ShiftEnd time,
@HoursWorked decimal(18,2)
)
AS
BEGIN

INSERT INTO tblShifts(
EmpID,
Date,
ShiftStart,
ShiftEnd,
HoursWorked
)


SELECT EmpID, @Date, @ShiftStart, @ShiftEnd, @HoursWorked
FROM tblEmployee WHERE LName=@LName AND FName=@FName

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

DATE is a keyword word and probably reserved (depends on the version and reserved by ODBC), so it is a lousy name for a column:

INSERT INTO tblShifts(EmpID, [Date], ShiftStart, ShiftEnd, HoursWorked)
    SELECT EmpID, @Date, @ShiftStart, @ShiftEnd, @HoursWorked
    FROM tblEmployee
    WHERE LName = @LName AND FName = @FName;

Of course, you also need the END for the end of the stored procedure.

Upvotes: 2

Related Questions