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'
Pradip PandeyPosted Apr 18, 2013, 11:17 PM
Please check this code
SELECT
@ID = Max(ID)
FROM tblAssignedMilestones
WHERE JobID = @JobID AND @Milestone = (SELECT ID FROM tblMilestones WHEREMilestone = @Milestone)
In the above statement, @Milestone is declared as input parameter which is of varchar type and you are trying assign int value in it.
Hope it will help.
Anthony ClarkePosted Apr 20, 2013, 4:21 AM
Thanks again :)
Pradip PandeyPosted Apr 19, 2013, 11:24 PM
Please mark it as Accepted Answer if it helped you.
Hope it will help.
Anthony ClarkePosted Apr 19, 2013, 4:41 AM
Jignesh TrivediPosted Apr 18, 2013, 11:48 PM
hi,
casting from int to varchar is not a problem
i have test this it worked for me
DECLARE @1 varchar(10)
set @1 = 12323
print @1
From your error, i think there is problem with your @Milestone parameter.
Also one suggestion use SCOPE_IDENTITY() if ID column is Identity.
hope this will help you.