Hi, Can somebody please help me with this. When i execute it, it says cannot convert varchar value 'jgj' to datatype int and NULL's the staffID
Please help
ALTER
PROCEDURE [dbo].[addAssignedMilestone]
(
@JobID
AS INT,
@Milestone
AS VARCHAR(500),
@TargetDate
AS DATETIME,
@StaffName
AS VARCHAR,
@ActualDate
AS DATETIME,
@Memo
AS VARCHAR(500)
)
AS
DECLARE
@ID AS int
DECLARE
@MilestoneID AS INT
DECLARE
@StaffID as INT
SET
@ID = 0
SET
@MilestoneID = (SELECT ID FROM tblMilestones WHERE Milestone = @Milestone)
SET
@StaffID = (SELECT ID from tblStaff WHERE surname + ', ' + forename = @StaffName)
BEGIN
INSERT
INTO tblAssignedMilestones (JobID,MilestoneID, TargetDate, AssignedStaffID, ActualDate, Memo)
VALUES
(@JobID,@MilestoneID, @TargetDate,@StaffID, @ActualDate,@Memo)
SELECT
@ID = Max(ID)
FROM tblAssignedMilestones
WHERE JobID = @JobID AND @Milestone = (SELECT ID FROM tblMilestones WHERE Milestone = @Milestone)
END
SELECT
@ID
EXEC
addAssignedMilestone 101, 'jgj' , '01-Apr-2013','Smith, Pete', '02-Apr-2013', 'm'
ALTER
PROCEDURE [dbo].[addAssignedMilestone]
(
@JobID
AS INT,
@Milestone
AS VARCHAR(500),
@TargetDate
AS DATETIME,
@StaffName
AS VARCHAR,
@ActualDate
AS DATETIME,
@Memo
AS VARCHAR(500)
)
AS
DECLARE
@ID AS int
DECLARE
@MilestoneID AS INT
DECLARE
@StaffID as INT
SET
@ID = 0
SET
@MilestoneID = (SELECT ID FROM tblMilestones WHERE Milestone = @Milestone)
SET
@StaffID = (SELECT ID from tblStaff WHERE surname + ', ' + forename = @StaffName)
BEGIN
INSERT
INTO tblAssignedMilestones (JobID,MilestoneID, TargetDate, AssignedStaffID, ActualDate, Memo)
VALUES
(@JobID,@MilestoneID, @TargetDate,@StaffID, @ActualDate,@Memo)
SELECT
@ID = Max(ID)
FROM tblAssignedMilestones
WHERE JobID = @JobID AND @Milestone = (SELECT ID FROM tblMilestones WHERE Milestone = @Milestone)
END
SELECT
@ID
EXEC
addAssignedMilestone 101, 'jgj' , '01-Apr-2013','Smith, Pete', '02-Apr-2013', 'm'