Click here to Skip to main content
Click here to Skip to main content
Go to top

Discover SQL: Dynamically recomputing columns

, 17 May 2002
Rate this:
Please Sign up or sign in to vote.
How to perform different computations for each row of a certain table, with each computation involving several columns.
<!-- Download Links --> <!-- Article image -->

Sample Image - ColumnsRecomputing.gif

<!-- Add the rest of your HTML here -->

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.

License

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

Share

About the Author

Adrian Bacaianu
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@yahoo.com).

Comments and Discussions

 
GeneralMy vote of 5 Pinmembermanoj kumar choubey28-Feb-12 18:32 
GeneralMy vote of 5 PinmemberS Jude Antony Deepak12-Aug-10 1:34 
GeneralThanks a looot [modified] PinmemberPL014-Nov-07 22:54 
Question@@rowcount test Pinmemberunlabeled18-Nov-05 7:04 
just curious -
why do you do this:
 
declare @rows as int
set @rows = @@rowcount
if not @rows > 0 return
 
instead of just this:
if not @@rowcount > 0 return
?
 
Seems to me to work either way, but maybe there is an issue I'm not thinking of?
 

 
Cheers,
ian
AnswerRe: @@rowcount test PinmemberAdrian Bacaianu19-Nov-05 22:54 
GeneralNice idea PinsussAnonymous15-Aug-02 9:18 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 18 May 2002
Article Copyright 2002 by Adrian Bacaianu
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid