Hi Guys,

Brand new to procedures / fiunctions in SQL so can anyone give me a REALLY basic example of the code to go in betweenb the BEGIN / END ?

BEGIN
i.e select something from the database and sent out to the created parameters?
END

Thanks,

Phil.

Hi Philip,

How about this:

CREATE PROCEDURE "ItemTotal" (IN "Number" INTEGER, OUT "Result" FLOAT)
BEGIN

DECLARE aCursor CURSOR FOR aStmt;
DECLARE aResult Float;

SET aResult = 0;

IF Number > 0 THEN
  PREPARE aStmt FROM 'select sum(quantity * price) as Total from
InvoiceItems where number = ?';
  OPEN aCursor USING Number;

  FETCH FIRST FROM aCursor ('Total') INTO aResult;

  CLOSE aCursor;
  UNPREPARE aStmt;
END IF;

SET Result = aResult;

END

This selects the subtotal of an invoice based upon the total of the invoice
items.  It takes the invoice number as a input parameter and sends the
results in a out parameter called Result.

---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

Besides the excellent example from Terry, you can browse the online manuals
(http://www.elevatesoft.com/manual?action=contents&id=edb2sql) which contain
numerous examples.  One of the simpler ones can be found under the
definition for ROWCOUNT:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=ROWCOUNT


David Cornelius
Cornelius Concepts

Thanks Terry and David... Much appreciated

Is it just me or does the ElevateDBManager program SQL windows have a real issue.

Copying, pasting, typing and the screen is not updated, so what is actually there is not what gets saved most of the time and the insert mode with text still overwrites the text.

Trying to use it to create some procedures and I have to keep using NOTEPAD and then pasting it in otherwise the text is not what it looks like.

Antother frustrating feature is when you ALTER procedure window and there's a mistake, it falls over and your left to enter all the data again, it does ask to open the SQL in a window and try it from there, but of course since the editor is all messed up it's just a little frustrating.

I'm using the UNICODE version of the ElevateDBManager if that makes a difference?

Philip,

> Copying, pasting, typing and the screen is not updated, so what is
> actually there is not what gets saved most of the time and the insert mode
> with text still overwrites the text. Trying to use it to create some
> procedures and I have to keep using NOTEPAD and then pasting it in
> otherwise the text is not what it looks like.

If you have 2.05 build 9, download a new copy of it from the ElevateSoft web
site.  There was a problem with the initial build 9 and the editor.  Tim was
kind enough to correct the issue and repost a new build with the corrections
in place.


> Antother frustrating feature is when you ALTER procedure window and
> there's a mistake, it falls over and your left to enter all the data
> again, it does ask to open the SQL in a window and try it from there, but
> of course since the editor is all messed up it's just a little
> frustrating.

Once you get the updated build, this won't be an issue.  I tend to do most
of my procedure development right within the SQL window, letting the syntax
check of the prepare statement tell me if I've made any major mistakes.

For more complicated stuff, I design them as scripts with hardcoded
variables in place of the input/output parameters and use the built in
debugger to walk through the code for testing.


> I'm using the UNICODE version of the ElevateDBManager if that makes a
> difference?

It does not.

---------------------------------------
Terry Swiers
Millennium Software, Inc.
http://www.1000years.com
http://www.atrex.com
---------------------------------------

Thanks Terry, Your a legend for pointing that out.

Tim I thank you also for fixing up the release so quickly.

Philip,

<< Tim I thank you also for fixing up the release so quickly. >>

No problem - it was a bone-headed attempt to do a quick fix for a Chinese
character display issue.  I should know better.

--
Tim Young
Elevate Software
www.elevatesoft.com