At 09:05 AM 29/06/2008, you wrote:

>Hi,
>
>Can I use 'Execute Block Statement" in a stored procedure?

No. Execute Block provides a syntax to enable you to run executable PSQL code in the dynamic SQL context. PSQL is already what you do when you write stored procedures. There is no requirement to ride a bike inside a motor car. ;-)

>If not so, how can we make use of this new feature?

At the most trivial level, it's just another form of expression syntax for deriving output in DSQL. At the complex end, it hands you a powerful way to run executable code from a DSQL statement - or to launch a nuclear attack on a database from a single statement using an SQL tool with a DSQL interface. As with any new thing, test its capabilities on some data that nobody actually uses for anything, and keep it by as something you might need to use some time for some very tricky operation.

If you are not using prepared, parameterised DSQL statements then it's unlikely you would find an immediate use for EXECUTE BLOCK that improves on what you can do with existing DML syntax. But, then, if you haven't yet discovered parameterised DSQL and prepared statements, you're probably not ready for EXECUTE BLOCK. ;-)

An input parameter for the block is visible in the prepared statement that's using the block. A trivial example of how you might use the syntax:

select
field1,
field2,
field3,
EXECUTE BLOCK (X SMALLINT = :X)
RETURNS (Y VARCHAR(25))
AS
BEGIN
Y = 'YADA TODAY IS ' ;
IF (X IS NOT NULL) THEN
Y = Y|| CAST (X AS VARCHAR(6));
ELSE
Y = Y|| 'UNSPECIFIED';
SUSPEND;
END
as yada,
anotherfield,
....
from atable
where field4 = ?
and x = ?

There's no guarantee that your programming language driver (whatever it is) will handle the extra parameter correctly, though, so you would need to investigate that and possibly seek out an updated driver version.

And - yeah - to anticipate your next question: That particular section of the Fb 2.0.x notes seems to have slithered under the radar of the Thought Police...I do need to chase our developers for a better piece of sample code for the release notes.

./heLen