Click here to Skip to main content
15,616,585 members
Articles / Database Development / SQL Server
Posted 17 May 2002


36 bookmarked

Discover SQL: Dynamically recomputing columns

Rate me:
Please Sign up or sign in to vote.
4.67/5 (11 votes)
17 May 20023 min read
How to perform different computations for each row of a certain table, with each computation involving several columns.

Sample Image - ColumnsRecomputing.gif


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.

Image 2

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:


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 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:
Image 3
Now, made update to the arg1 column, using the fallowing update statement:

update computation 
set arg1 = arg1 +10;

Look now at the results:
Image 4

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.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By
Web Developer
Romania Romania
I make programming for over 4 years and extensive experience in C++, ASP, Pascal, MFC, COM+, ATL, TCP/IP, HTTP protocols, XML, XSL, SOAP and SQL.
For the last 2 years i working extensively at the background of financial sites (databases, n tier architecture).

I’m available for contracts and/or outsourcing (<Adrian Bacaianu>

Comments and Discussions

GeneralMy vote of 5 Pin
Manoj Kumar Choubey28-Feb-12 18:32
professionalManoj Kumar Choubey28-Feb-12 18:32 
GeneralMy vote of 5 Pin
S Jude Antony Deepak12-Aug-10 1:34
S Jude Antony Deepak12-Aug-10 1:34 
GeneralThanks a looot [modified] Pin
pluniewski4-Nov-07 22:54
pluniewski4-Nov-07 22:54 
Question@@rowcount test Pin
unlabeled18-Nov-05 7:04
unlabeled18-Nov-05 7:04 
AnswerRe: @@rowcount test Pin
Adrian Bacaianu19-Nov-05 22:54
Adrian Bacaianu19-Nov-05 22:54 
GeneralNice idea Pin
Anonymous15-Aug-02 9:18
Anonymous15-Aug-02 9:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.