In part 1 of this article, we have prepared the table for the update. In this second part, we will learn how to update the SQL table with only the rows modified by the user.
Part 2: Tracking Changes and Updating the SQL Table
Tracking the changed rows
We need a way to track what has been changed by the user in order to update the SQL table with only the modified rows and thus avoid any unnecessary workload on the server.
This is where the “XL3TrackChanges” function comes into place. We will use it to flag changed rows, which will then make it possible to isolate them thanks to the “XL3Filter” function.
Here the syntax:
XL3TrackChanges( Clear, Input, Output )
Where the parameters are :
ParameterDescriptionClearA cell reference that should contain TRUE when the list of tracked changed rows should be cleared.InputThe range to watch for changes.OutputThe range to mark when change occur.
So in our example, the formula is:
XL3TrackChanges(T5, salary:commission, changed )
Where:
ParameterDescriptionT5When T5 is set to TRUE, all the tracked changes (value equals TRUE in the “Output” range – i.e. column P) will be cleared.salary:commissioni.e. the named ranges we defined for columns N and O.changedThe range of cells XL3TrackChanges will update by setting the value TRUE for each modified row (i.e. column P).
Here is what it looks like:
So, now, whenever a value is changed in the “salary” or “commission” range (i.e. columns N and O), XL3TrackChanges writes TRUE in the “changed” column (i.e. column P):
Thanks to the “XL3Link” formula we defined earlier for the “Clear Edits” button that sets $T$5 to TRUE, the marked changes in the “changed” named range (column P) will be cleared when clicked.
To summarize how the whole thing works:
- When clicking the “Clear Edit” link in $N4$, the “XL3Link” function set $T$5 to TRUE
- This fires the “XL3RefreshObjects” formula in $U$5 (please refer to part 1 of this article): the table gets refreshed, reverting any changed values to the originals
- It also fires the “XL3TrackChanges” formula in $S$5: the “changed” range (i.e. column P) gets cleared
Updating the SQL table
Rather than executing multiple individual SQL “Update” commands, we want to make use of an SQL stored procedure in conjunction with a User Defined Table Type to bulk update the SQL table.
A Stored Procedure using a User Defined Table Type
In this example, we defined the following in the SQL database:
- A table type made of 13 columns (as we display 13 columns in our query):
CREATE TYPE [dbo].[XL3ParameterTable13] AS TABLE(
[ParameterValue1] [nvarchar](100) NULL,
[ParameterValue2] [nvarchar](100) NULL,
[ParameterValue3] [nvarchar](100) NULL,
[ParameterValue4] [nvarchar](100) NULL,
[ParameterValue5] [nvarchar](100) NULL,
[ParameterValue6] [nvarchar](100) NULL,
[ParameterValue7] [nvarchar](100) NULL,
[ParameterValue8] [nvarchar](100) NULL,
[ParameterValue9] [nvarchar](100) NULL,
[ParameterValue10] [nvarchar](100) NULL,
[ParameterValue11] [nvarchar](100) NULL,
[ParameterValue12] [nvarchar](100) NULL,
[ParameterValue13] [nvarchar](100) NULL
)
GO
- A stored procedure which takes a “XL3ParameterTable13” table type as only argument to manage the update:
CREATE PROCEDURE [dbo].[ap_UpdateEmployee2] @EmployeeValues XL3ParameterTable13 READONLY
AS
BEGIN
SET NOCOUNT ON;
Update Employees
Set salary = ISNULL(CAST(v.ParameterValue11 as decimal(10,2)), Employees.salary),
commission_pct = ISNULL(CAST(v.ParameterValue12 as decimal(10,2)), Employees.commission_pct)
from @EmployeeValues v
Where Employees.employee_id = CAST(v.ParameterValue1 AS int)
END
Executing the stored procedure
To execute our stored procedure we make use of the “XL3RunSqlProc2” function.
Here the syntax:
XL3RunSqlProc2(ExcecuteSQL, Connection, ProcName, [Parameter1Name], [Parameter1Value],..., [ParameterNName], [ParameterNValue])
Where the parameters are :
ParameterDescriptionExcecuteSQLThe cell that will fire the SQL3RunSqlProc2 when set to TRUE. ConnectionThe workbook connection ID use to connect to the SQL database. ProcNameThe name of the stored procedure to run [Parameter1Name], [Parameter1Value]The name and value for the parameter to be passed to the stored procedure. You can pass up to 10 pairs.
Passing only the changed rows as argument to the stored procedure
We now have the required syntax to execute our stored procedure but how do we pass the “Parameter1Value” for the “EmployeeValues” argument to our “XL3RunSqlProc2” function? In other words, how do we pass a table containing only the changed rows?
This is when the “XL3Filter” function come in to play. Here its syntax:
XL3Filter( Input, Filter )
Where the parameters are :
ParameterDescriptionInputThe range to watch for changes. FilterThe range (column) that contains TRUE values to corresponding changed rows.
In our case, making use of the named ranges we defined:
XL3Filter(data,changed)
The above capture is only here to help understand what the XL3Filter does. In our example, we actually don’t need to display it but rather pass it as the value argument to our stored procedure.
Bringing all the pieces together
We can now merge all the pieces together. The final syntax to execute our stored procedure becomes:
XL3RunSqlProc2(P5,1,"ap_UpdateEmployee2","EmployeeValues",XL3Filter(data,changed))
Where:
ParameterDescriptionP5When P5 = TRUE, the stored procedure will fire. 1The XLCubed connection ID for the SQL database used in our workbook ap_UpdateEmployee2The name of our stored procedure EmployeeValuesThe name of the argument for our stored procedureXL3Filter(data,changed)The value for the argument which is a table containing only the changes rows.
Of course, we need an additional “XL3Link” formula to set $P$5 to TRUE and thus trigger the “XL3RunSqlProc2” function that will in turn execute our stored procedure. This is what the formula associated to the “Save” link in $O$4 does:
XL3Link(,"Save",,XL3Address(P5),TRUE,XL3Address($T$5),TRUE)
So, let’s click that “Save” button and update our SQL table!
