Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009

    Question Unanswered: Return Table from User-Defined Function?

    After some quick experimentation, I'm looking for some feedback from those more experience with the Pervasive SQL engine. Basically, I am attempting to implement a UDF that takes a CSV argument and returns the elements in a table. The intent is to call the UDF from a construct in a stored procedure similar to this:

    WHERE id IN (SELECT id FROM udf_Split('''6'', ''7'', ''42'''))

    The road block is that it appears you cannot return a table from a UDF in Pervasive as I am getting a syntax error shortly after the BEGIN statement. In looking at CREATE FUNCTION's help entry, it appears this would be because TABLE cannot be used with the RETURNS. Is this indeed not supported in Pervasive?

    If not, does anyone have a good solution for this limitation? I'm thinking a temp table created from the main app is the only way to go otherwise.

  2. #2
    Join Date
    Dec 2001
    Provided Answers: 6
    I haven't tried it but you might want to have the function return a string and then build the Select with the result and run the SQL using EXEC.
    Something like:
    declare :sSQL string;
    set :sSQL = "select from test";
    exec (:sSQL);
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts