Discover SQL: Dynamically recomputing columns

• Download DB backup files - 78 Kb

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.