Archives of the TeradataForum

Message Posted: Mon, 11 Nov 2008 @ 01:16:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Does Stored Procedure allow multi statement transactions?

I am written a Stored Procedure that selects a value and returns that values as an output parameter and then update that same value that it just read. This Stored Procedure could be called by many users, so I want to lock the table so no one else can read it while another update is being done thereby getting the wrong value

Here is a simplifed version of the stored procedure

     sel value
          into :output_value
     from tablea

     ; update tablea
         set value = value + 1;

The problem is when I create the Stored Procedure using SQL Assisant, it moves the semi-colon so it is no longer a multi-statment transaction.

     sel value
          into :output_value
     from tablea
     ;
       update tablea
         set value = value + 1;

why?

	<Prev	Next>		<<First	<Prev	Next>	Last>>