I hope these are fairly simple questions:

- I've been able to use a select similar to:     select top 100 from
blahblahblah. Does ElevateDB have a "top" equivalent?
-Something similar to this works in other DBs:
   declare xyz int;
   select xyz=field1 from table1          Is there any equivalent to this
in ElevateDB?
-Is there any way to return a function value within a stored proc? I've been
OK so far with something like:
   select field1 from table1 where field2=function1()     Is having an
output parameter in the stored proc, then setting it = to the function the
right way for me to go if all I need to do is return a function value?
-I've used IF EXISTS (select field1 from table1). IF NOT EXISTS (...) also
works elsewhere. It looks like ElevateDB uses EXISTS with subqueries though.
Is there a fast way to check for an existing record before doing other
"things"?


Thanks

jr

And what's the right way nest stored procs? Is there a limitation to the
nesting levels?

thanks


jr



"James Relyea" <JRelyea@JBRSoftware.com> wrote in message
news:CDC90B31-60D0-4A2D-AFE9-F99EE082028E@news.elevatesoft.com...
>I hope these are fairly simple questions:
>
> - I've been able to use a select similar to:     select top 100 from
> blahblahblah. Does ElevateDB have a "top" equivalent?
> -Something similar to this works in other DBs:
>    declare xyz int;
>    select xyz=field1 from table1          Is there any equivalent to this
> in ElevateDB?
> -Is there any way to return a function value within a stored proc? I've
> been OK so far with something like:
>    select field1 from table1 where field2=function1()     Is having an
> output parameter in the stored proc, then setting it = to the function the
> right way for me to go if all I need to do is return a function value?
> -I've used IF EXISTS (select field1 from table1). IF NOT EXISTS (...) also
> works elsewhere. It looks like ElevateDB uses EXISTS with subqueries
> though. Is there a fast way to check for an existing record before doing
> other "things"?
>
>
> Thanks
>
> jr
>

I have something similar to the following in my stored proc that works, but
I'm not sure it's optimal or not:

....
....
prepare sql from 'select field1 from table1';
execute sql;
if rowsaffected>0 then
....
end if;




jr

"James Relyea" <JRelyea@JBRSoftware.com> wrote in message
news:CDC90B31-60D0-4A2D-AFE9-F99EE082028E@news.elevatesoft.com...
>I hope these are fairly simple questions:
>
> -I've used IF EXISTS (select field1 from table1). IF NOT EXISTS (...) also
> works elsewhere. It looks like ElevateDB uses EXISTS with subqueries
> though. Is there a fast way to check for an existing record before doing
> other "things"?

James,

<< - I've been able to use a select similar to:     select top 100 from
blahblahblah. Does ElevateDB have a "top" equivalent? >>

ElevateDB uses a RANGE clause:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=11&topic=171

<< -Something similar to this works in other DBs:
   declare xyz int;
   select xyz=field1 from table1          Is there any equivalent to this
in ElevateDB? >>

You need to use a cursor and the FETCH statement to do so in an ElevateDB
stored procedure/script/job.  See the examples here in the manual:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=13&topic=239
http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=13&topic=241

<< -Is there any way to return a function value within a stored proc? >>

Yes, use an OUT parameter:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=10&topic=165

<< I've been OK so far with something like:
   select field1 from table1 where field2=function1()     Is having an
output parameter in the stored proc, then setting it = to the function the
right way for me to go if all I need to do is return a function value? >>

If you want to use the return value in queries like you would a column
reference or constant value, then what you want is a function, not a stored
procedure:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=10&topic=162

<< -I've used IF EXISTS (select field1 from table1). IF NOT EXISTS (...)
also works elsewhere. It looks like ElevateDB uses EXISTS with subqueries
though. Is there a fast way to check for an existing record before doing
other "things"? >>

In what context ?  In a query, stored procedure, etc. ?

--
Tim Young
Elevate Software
www.elevatesoft.com

James,

<< And what's the right way nest stored procs? Is there a limitation to the
nesting levels? >>

If you mean call another stored procedure, then you would use the CALL
statement:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=13&topic=233

--
Tim Young
Elevate Software
www.elevatesoft.com

James,

<< I have something similar to the following in my stored proc that works,
but I'm not sure it's optimal or not: >>

Optimal in what sense ?  Performance ?  It is functionally correct, if
that's what you're asking.

--
Tim Young
Elevate Software
www.elevatesoft.com

Sorry. It looked like the rowsaffected was working for me, but I didn't know
if that was the only or best or preferred method. I always ask about
"optimal" usage because maybe the engine's optimized for another way than
how I'm doing it. I'm used to having a ton of ways to get to the same end
result, but most of the time there's just 1 or 2 best ways to get there.


jr



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:887DFB89-5B56-40DF-93DF-7AA0C930D9B9@news.elevatesoft.com...
> James,
>
> << I have something similar to the following in my stored proc that works,
> but I'm not sure it's optimal or not: >>
>
> Optimal in what sense ?  Performance ?  It is functionally correct, if
> that's what you're asking.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

James,

<< Sorry. It looked like the rowsaffected was working for me, but I didn't
know if that was the only or best or preferred method. I always ask about
"optimal" usage because maybe the engine's optimized for another way than
how I'm doing it. I'm used to having a ton of ways to get to the same end
result, but most of the time there's just 1 or 2 best ways to get there. >>

Ahh, okay.  In optimal terms, if you're trying to test for the existence
of rows in SQL, you can create a base function like this that will do most
of the heavy lifting for you:

CREATE FUNCTION "RowExists" (IN "SQLToExecute" VARCHAR)
RETURNS BOOLEAN
BEGIN
  DECLARE TempCursor CURSOR FOR TempStmt;

  PREPARE TempStmt FROM SQLToExecute;
  OPEN TempCursor;
  RETURN ROWCOUNT(TempCursor) > 0;
END

Here is a test script:

SCRIPT
BEGIN
  IF ROWEXISTS('SELECT * FROM customer WHERE State=''FL''') THEN
     SET LOG MESSAGE TO 'Row Exists';
  END IF;
END

--
Tim Young
Elevate Software
www.elevatesoft.com