Hi,

I created this simple stored proc:

BEGIN
DECLARE Result CURSOR WITH RETURN FOR Stmt;
  BEGIN
  PREPARE Stmt FROM 'SELECT * from MyTable where UserID = ?';
  OPEN Result;
  END;
END

Also I created a parameter "UserID". When executing the proc the result are 0 records.
When I replace the param by a constant it works fine.
Tested this in ElevateDB Manager.

What am I doing wrong?

Felix

Felix,

Replace:  OPEN Result; with: OPEN Result USING UserID;

Regards, Harry

"Felix" <test@test.com> schreef in bericht
news:4E8222C2-EF28-48C3-93B5-A72BE5820E87@news.elevatesoft.com...
> Hi,
>
> I created this simple stored proc:
>
> BEGIN
> DECLARE Result CURSOR WITH RETURN FOR Stmt;
>    BEGIN
>    PREPARE Stmt FROM 'SELECT * from MyTable where UserID = ?';
>    OPEN Result;
>    END;
> END
>
> Also I created a parameter "UserID". When executing the proc the result
are 0 records.
> When I replace the param by a constant it works fine.
> Tested this in ElevateDB Manager.
>
> What am I doing wrong?
>
> Felix
>

"Harry de Boer" <harry@staaf.nl> wrote:

Replace:  OPEN Result; with: OPEN Result USING UserID;

Thank you, Harry. Any idea where to find that in a manual or help-file just to understand it?

Regards Felix

"Harry de Boer" <harry@staaf.nl> wrote:

Replace:  OPEN Result; with: OPEN Result USING UserID;

No need to answer: this seems to be general SQL stuff. I'll get the information from a
good book

Thanks. Felix

Felix,

<< No need to answer: this seems to be general SQL stuff. I'll get the
information from a good book >>

Just a quick note - the dynamic SQL used in the SQL/PSM implementation in
ElevateDB is non-standard for stored procedures, but it is standard dynamic
SQL syntax for client-side dynamic SQL.  So, to get information about
PREPARE, UNPREPARE, EXECUTE, and EXECUTE IMMEDIATE, you'll need to consult
the Dynamic SQL section of any good SQL book.  For example, in Jim Melton's
excellent SQL1999 book, these statements are covered in Chapter 18 - Dynamic
SQL.

Some implementations of dynamic SQL in stored procedures has used completely
funky and vendor-specific syntax (Oracle, for example).   I thought it best
that if we're going to use dynamic SQL in the stored procedures, that it at
least be standard dynamic SQL syntax.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I know you've recommended this book before so I just had a look on Amazon - the price ranged from £20 (used) - £150 (new). I might buy the used one

Roy Lambert

Roy,

Following Tim's recommendation I ordered a new one at Amazon.co.uk about
2 months ago and I'm still waiting... I'm afraid the book will be
outdated when I finally receive it

--
Fernando Dias


Roy Lambert escreveu:
> Tim
>
> I know you've recommended this book before so I just had a look on Amazon - the price ranged from £20 (used) - £150 (new). I might buy the used one
>
> Roy Lambert

Fernando


How come you're ordering from the UK site?

Roy Lambert

Roy

Well, inside EU the choices are UK, France and Germany.
Since my French is even worst then my English and I don't understand
German at all, I chose UK. Also, I thought it would be faster then
ordering from US.

--
Fernando Dias

I'm starting to think learning German would be easier
I meant "worst than my English" and "faster than ordering".
Sorry.

> Well, inside EU the choices are UK, France and Germany.
> Since my French is even worst then my English and I don't understand
> German at all, I chose UK. Also, I thought it would be faster then
> ordering from US.