CODEWITHSUNDEEP
sqlstored-proceduresdb2
Patrick
Patrick

Reputation:

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

Is it possible, by using a stored procedure, to fetch an integer column value from resultset into a local variable, manipulate it there and then write it back to the resultset's column?

If so what would the syntax look like?

Upvotes: 0

Views: 289

Answers (4)

KM.
KM.

Reputation: 103607

Why not just do the manipulation 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

Upvotes: 0

John Sansom
John Sansom

Reputation: 41839

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.

Upvotes: 1

Patrick
Patrick

Reputation:

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... :-)

Upvotes: 0

Thorsten
Thorsten

Reputation: 13181

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.

Upvotes: 0

Related Questions