Discover SQL: Dynamically recomputing columns






4.67/5 (10 votes)
May 18, 2002
3 min read

75474

658
How to perform different computations for each row of a certain table, with each computation involving several columns.
Introduction
Our problem is to perform a different computation for each row of a certain table, with each computation involving several columns. Computed columns aren't adequate for this scenario because you can't store different computation for each row in one computed column.
Using expressions in queries is a common practice. But did you know that you can base a column in a query's resultset on a computation rather than simply using the query to return a base column from a table? If you use the same computation in many queries against the same table, storing the computation in the base table as a computed column shortens your queries and results in less required code maintenance.
SQL server 7.0 introduced computed columns, SQL server 2000 added the ability to create indexes on computed columns, so the results can be stored on disk. An index on a computed column saves the CPU time needed to perform the calculations and allows efficient filtering and sorting.
Developing
To see how to solve our problem, let's look at a "Computation" table structure.
The tables computation column, encapsulated in an Unicode character string, will store computations that refer to any of the columns arg1, arg2, or arg3. Some examples of possible values you can store in the computation column are:
N'@arg1+@arg2+@arg3'
N'@arg1*@arg2-@arg3'
N'CEILING(@arg1+@arg2/@arg3)'
We want to automatically compute in the "rez " column the result of an update/insert of arg1, arg2 or arg3 columns; the result given by the formula stored in the "computation" column. That fact will be calculated with different formula on every row.
The engine of what happening when are performed an INSERT
or an UPDATE are stored in the trigger trgComputation of Computation table.
CREATE TRIGGER trgComputation ON [dbo].[Computation] FOR INSERT, UPDATE
AS
declare @rows as int
set rows = @@rowcount
if not @rows > 0 return
if not update(arg1) and not update(arg2) and not update(arg3) return
declare
@key as int,
@arg1 as int,
@arg2 as int,
@arg3 as int,
@rez as int,
@comp as nvarchar(500),
@param as nvarchar(500)
select @key = min(id) from inserted
while @key is not null
begin
select
@arg1 = arg1,
@arg2 = arg2,
@arg3 = arg3,
@comp = computation
from inserted where @key = id
set @comp = N'set @rez= ' + @comp
set @param = N'@rez int output, @arg1 int, @arg2 int, @arg3 int'
exec sp_executesql @comp, @param, @rez output, @arg1, @arg2, @arg3
update computation set rez=@rez where id= @key
select @key = min(id) from inserted where id>@key
end
At the beginning, the trigger checks whether the INSERT or UPDATE operation that activated it affected any rows; if not the trigger doesn't need to do anything more.
Next, the trigger performs a loop that iterates through all rows in the "INSERTED" table, witch holds all rows that were inserted or modified in the base table, Computation.
In the body of the loop, the code fetches the values of the input arguments and the computation from the current row in INSERTED and stores these values in local variables.
Now the code needs to dynamically perform the computation stored in the @comp variable and pass the value from the @resultvariable inside the computation to the trigger's @rez variable. To achieve this result, you can use an undocumented feature of the sp_executesql system stored procedure, witch lets you use output parameters:
DECLARE @r_out as int
EXEC sp_executesql
N'set @r = @p1 * @p2',
N'@p1 int, @p2 int, @r int output',
@r = @r_out OUTPUT,
@p1 = 10,
@p2 = 5
SELECT @r_out
This simple script example uses sp_executesql to calculate the product of two input arguments and stores the result in an output parameter called @r.
The first argument that sp_executesql accepts is the statement that you want to execute dynamically, The second argument is a string that holds the list of input and output parameters that the statement uses. All the other arguments that sp_executesql accepts are assignments of values to the parameters that the statement uses.
Lets try the procedure now. Look at our table at that moment of time:
Now, made update to the arg1 column, using the fallowing update statement:
update computation set arg1 = arg1 +10;
Look now at the results:
The sp_executesql system stored procedure lets you perform computations dynamically and even use output parameters. Without the ability to perform the computations dynamically, maintaining a different computation for each row in the table would be unnecessarily complicated.
This article is inspired from SQL server magazine.