James Elliott
asked on
Append Update?
Hi All,
Is it possible to 'add' to an existing value within an update statement?
Basically I have the stored procedure below:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc dbo.ash_add_holiday_reques t(
@start DATETIME,
@end DATETIME,
@hrs FLOAT,
@staff_id INT
)
AS
SET NOCOUNT ON
BEGIN
INSERT INTO dbo.ash_holiday_requests
VALUES(@staff_id, @start, @end,@hrs,0,NULL,0,0)
SELECT IDENT_CURRENT('dbo.ash_hol iday_reque sts')
DECLARE @old_planned_hours =
(SELECT hol_planned
FROM dbo.ash_holiday
WHERE staff_id = @staff_id)
UPDATE dbo.ash_holiday
SET hol_planned = @hrs + @old_planned_hours
WHERE staff_id = @staff_id
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
As you can see, I'm retrieving an existing value of planned_hols so that I can 'add' to it. Is there an easier way of doing this?
Thanks
Jell
Is it possible to 'add' to an existing value within an update statement?
Basically I have the stored procedure below:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc dbo.ash_add_holiday_reques
@start DATETIME,
@end DATETIME,
@hrs FLOAT,
@staff_id INT
)
AS
SET NOCOUNT ON
BEGIN
INSERT INTO dbo.ash_holiday_requests
VALUES(@staff_id, @start, @end,@hrs,0,NULL,0,0)
SELECT IDENT_CURRENT('dbo.ash_hol
DECLARE @old_planned_hours =
(SELECT hol_planned
FROM dbo.ash_holiday
WHERE staff_id = @staff_id)
UPDATE dbo.ash_holiday
SET hol_planned = @hrs + @old_planned_hours
WHERE staff_id = @staff_id
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
As you can see, I'm retrieving an existing value of planned_hols so that I can 'add' to it. Is there an easier way of doing this?
Thanks
Jell
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to be of help :)
ASKER
Ty
Jell