CODEWITHSUNDEEP
sql-servert-sql
Alistair Morris
Alistair Morris

Reputation: 23

Update followed by insert into same table using stored procedure

I have the following Jobs table where jobs are going to rotate every 28 days.

After adding a new Customer, I will initially create one job manually.

I'd like to updatethe Complete column to true, and have a new record inserted into the same table with a new job specifying exactly the same details only with a new date (28 days later) with Complete and Paid values as false.

I tried using a FOR UPDATE trigger unsuccessfully and would prefer to use a stored proc.

How can this be done?

CREATE TABLE [dbo].[Jobs](
    [JobID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NOT NULL,
    [JobDate] [date] NOT NULL,
    [Price] [decimal](7, 2) NOT NULL,
    [Complete] [bit] NOT NULL,
    [Paid] [bit] NOT NULL,
CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED
CONSTRAINT [FK_CustomerID] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customer] ([CustomerId])

Upvotes: 2

Views: 904

Answers (1)

p.campbell
p.campbell

Reputation: 100607

Try this to update the existing record and create a new one based on it:

CREATE PROC MyProc

@CustomerID int

AS

DECLARE @JobID int;
SELECT TOP 1 @JobID =  JobID 
FROM Jobs WHERE CustomerID = @CustomerID AND Complete = 0
ORDER BY JobDate DESC;

UPDATE Jobs
SET Complete = 1
WHERE JobID = @JobID;

INSERT Jobs(CustomerID, JobDate, Price, Complete, Paid)
  SELECT CustomerID, DATEADD(m,1,JobDate), Price, 0, 0
  FROM Jobs WHERE JobID = @JobID;

Upvotes: 2

Related Questions