Rob
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.
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)
);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No, you can use SP even for a single SQL SELECT command but that's not obvious.
The SP advantage is in easier maintainability and variability - you may change it without app update (if your SQL admins allow).
SPs are also faster than single SQL commands because they are precompiled.
The SP advantage is in easier maintainability and variability - you may change it without app update (if your SQL admins allow).
SPs are also faster than single SQL commands because they are precompiled.
ASKER
Great info. Thanks for your help
ASKER