Anthony Clarke

Anthony Clarke

  • NA
  • 18
  • 36.7k

SQL problem, stressing me

Apr 18 2013 12:04 PM

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'


 


Answers (5)