When adding an entry to a database it would be nice to combine the addition with an update.
For example adding a new entry if it already exists then that entry can be updated.
This means putting more of the sequence within the database stored procedure. But it will save on a database query, with possible action – the addition, and a result confirming that the addition occurred, or a value to denote that it already exists.
If the entry already exists then there is a further call to the database to update that entry.
Dependant upon the circumstance it may be better to combine this sequence all within a single stored procedure call to the database.
There is the option to check if a particular row value exists. If it does then update or add a new entry.
Here’s the initial setup of the stored procedure, with the passed in parameters:
CREATE PROCEDURE AddUpdateProduct @ProductId INT @Name NVARCHAR(150) @Details NVARCHAR(max)
Lets begin with a classic update
UPDATE Product SET Name = @Name, Details = @Details WHERE ProductId = @ProductId
Similarly an insert would look like:
INSERT INTO Product ( Name, Details ) VALUES ( @Name, @Details ) SELECT SCOPE_IDENTITY()
I’ve added SELECT SCOPE_IDENTITY(), to retrieve the ID of the added entry.
In our example we wish to combine the two together. We’ll start by trying to update the entry.
A test on the rowcount will tell us whether we were successful. If no rows were affected then a new row is added.
Here’s the example with the combined code:
CREATE PROCEDURE AddUpdateProduct @ProductId INT @Name NVARCHAR(150) @Details NVARCHAR(max) UPDATE Product SET Name = @Name, Details = @Details WHERE ProductId=@ProductId IF @@ROWCOUNT =0 BEGIN INSERT INTO Product ( Name, Details ) VALUES ( @Name, @Details ) SELECT SCOPE_IDENTITY() END
A short piece of SQL to update an existing row in a database table, or if the row doesn’t exist then to add a new entry.
I like this idea. By putting the whole action in the SQL we are saving going backwards and forwards between the database server and our website software.