Writing the content of a local variable back to the resultset column?

Question

Hello!

Is it possible, by using a stored procedure, to fetch a column value from resultset into a local variable, manipulate it there and then write it back to resultset column? The column datatype is integer.

How would the syntax to write the value back look like?

Thanks in Advance... :-)

Answer 1

Something along the following lines should do the trick.

DECLARE @iSomeDataItem INT

SELECT @iSomeDataItem = TableColumName
FROM TableName
WHERE ID = ?

--Do some work on the variable
SET @iSomeDataItem = @iSomeDataItem + 21 * 2

UPDATE TableName
    SET TableColumName = @iSomeDataItem
WHERE ID = ?

The downside to an implementation of this sort is that it only operates on a specific record however this may be what you are looking to achieve.

Cheers, John

Answer 2

What you are looking for is probably more along the lines of a user-defined function that can be used in SQL just like any other built in function.

Not sure how this works in DB2, but for Oracle it would be something like this:

Create or replace Function Decrement (pIn Integer) 
return Integer
Is
Begin
  return pIn - 1;
end;

You could use this in a SQL, e.g.

Select Decrement (43)
From Dual;

should return the "ultimate answer" (42).

Hope this helps.

Answer 3

why not just do the manipulation right within the update statement??? you don't need to load it into a variable, manipulate it, and then save it.

update TableName SET TableColumnName=TableColumnName + 42 /* or what ever manipulation you want */ WHERE ID = ?

also, @iSomeDataItem + 21 * 2 is the same as: @iSomeDataItem + 42

the function idea is an unnecessary extra step, unless most of the following are true:

1) you will need to use this calculation in many places 2) the calculation is complex 3) the calculation can change

Answer 4

Hello! :-)

Thanks for the replies, i went another way and solved the problem without using a procedure. The core problem was to calculate a Date using various column values, the column values ahd to to converted to right format. Solved it by using large "case - when" statements in the select.

Thanks again... :-)