Avatar of Rob
Rob
Flag for Australia

asked on 

Transaction or Stored Procedure MSSQL 2012

Should this be a stored procedure? If so, how? if not, can I do this in a transaction that I can rollback if any of them error?

I have 4 commands to run: INSERT then UPDATE then INSERT then UPDATE

I have a feeling this isn't the best way to do this... and I'm trying to use my basic MySQL skills to do this project in MSSQL.

INSERT INTO ProductsLog (ID, logdate)
(SELECT ID, Getdate() FROM InactiveProperties WHERE p_id IS NULL AND (enabled=1))
;
UPDATE Products SET
enabled=0
WHERE ID IN (
SELECT ID FROM InactiveProperties WHERE p_id IS NULL AND (enabled=0)
);
INSERT INTO ProductsLog (PID, logdate)
(SELECT ID, Getdate() FROM InactiveProperties WHERE p_id IS NOT NULL AND (enabled=0))
;
UPDATE Products SET
enabled=1
WHERE ID IN (
SELECT ID FROM InactiveProperties WHERE p_id IS NOT NULL AND (enabled=0)
);

Open in new window

DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Rob

8/22/2022 - Mon