Hello, is this right, that I can't use this procedure

PROCEDURE "H" (IN "IN1" SMALLINT, IN "IN2" SMALLINT)
BEGIN  
DECLARE TempCursor CURSOR  WITH RETURN FOR stmt;


PREPARE stmt FROM  'select count(*)
                   from
                   (
                   select count(Zahl)
                   from Total
                   where Zahl in(?,?)
                   group by Runde
                   having count(Zahl) > 1
                   ) as t';

OPEN TempCursor USING IN1,IN2;

END

Or do I somthing wrong?

I get the error ...dynamic references parameters not allowed.


TIA
Dieter Nagy

Dieter

Whether it should be allowed or not I can't say, but I had something similar and what you'll need to do is adjust your procedure along the lines of


PROCEDURE "H" (IN "IN1" SMALLINT, IN "IN2" SMALLINT)
BEGIN
DECLARE SQLCode VARCHAR;
DECLARE TempCursor CURSOR  WITH RETURN FOR stmt;

SET SQLCode = 'select count(*)
from
(
select count(Zahl)
from Total
where Zahl in(' + IN1+','+IN2+')
group by Runde
having count(Zahl) > 1
) as t';


PREPARE stmt FROM SQLCode;


OPEN TempCursor;
END

Roy Lambert [Team Elevate]

Roy,
thanks for your help.

At First  I get the error ..Expected(Char, Guid or VarChar expression but instead found IN1

Then I tried it with CAST(IN1 AS Varchar).

Now it works.

THANKS!
Dieter Nagy







Roy Lambert wrote:

Dieter

Whether it should be allowed or not I can't say, but I had something similar and what you'll need to do is adjust your procedure along the lines of


PROCEDURE "H" (IN "IN1" SMALLINT, IN "IN2" SMALLINT)
BEGIN
DECLARE SQLCode VARCHAR;
DECLARE TempCursor CURSOR  WITH RETURN FOR stmt;

SET SQLCode = 'select count(*)
from
(
select count(Zahl)
from Total
where Zahl in(' + IN1+','+IN2+')
group by Runde
having count(Zahl) > 1
) as t';


PREPARE stmt FROM SQLCode;


OPEN TempCursor;
END

Roy Lambert [Team Elevate]

Dieter


Sorry about that - I ignored the fact that your parameters were numeric.

Roy Lambert [Team Elevate]

Roy

No problem. Thanks once again.

Dieter




Roy Lambert wrote:

Dieter


Sorry about that - I ignored the fact that your parameters were numeric.

Roy Lambert [Team Elevate]

Dieter,

<< Or do I somthing wrong?

I get the error ...dynamic references parameters not allowed. >>

No, you didn't do anything wrong.  Currently you cannot use parameters with
derived tables.  It's something that will be addressed after 2.03 is
released.

--
Tim Young
Elevate Software
www.elevatesoft.com