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.
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
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
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
@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
@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
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
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
N'set @r = @p1 * @p2',
N'@p1 int, @p2 int, @r int output',
@r = @r_out OUTPUT,
@p1 = 10,
@p2 = 5
This simple script example uses
sp_executesql to calculate
the product of two input arguments and stores the result in an output parameter
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:
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