Tablename as Parameter in stored function

[Ruslan Zasukhin]

On 18/10/07 5:19 PM, "Martin S." <blackfin at elfenherz.de> wrote:

> Bart Pietercil schrieb:
>> 
>> On 18-okt-07, at 15:54, Martin S. wrote:
>> 
>>> How can I pass a table name into a stored procedure?
>>> 
>>> This is my function:
>>> 
>>> CREATE FUNCTION NextAutoID(inValue string)
>>> RETURNS ULONG
>>> begin
>>>    DECLARE max_autoid ULONG ;
>>>    select MAX(AutoID) INTO max_autoid from  inValue ;
>>>    RETURN max_autoid + 1 ;
>>> end;
>>> 
>>> 
>>> but I get this error:
>>> error: Table "inValue" not found.

Hi Martin,

As far as I understand, you try implement function that will return next ID
for some table. Right? Then you try implement it in wrong way!

* You try use MAX(ID) function for that table. This is not best speed
because need do MAX() search, and this not provide you behavior of always
growing ID, because of wholes.

The better way can be next. Make in your db new table "IDs" with fields
{TableName, LastID }
then your function will looks as

CREATE FUNCTION NextID(inTable string)
RETURNS ULONG
BEGIN
   DECLARE max_autoid ULONG ;

      UPDATE IDs SET LastID = LastID + 1 WHERE TableName = @inTableName;
      SET nextID = SELECT LastID FROM IDs WHERE TableName = @inTableName;

   RETURN nextID
END;




-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]