🔒 Locked

[web] create function return table

Started by akira32 Jan 1, 2009 at 12:09 AM 5 replies 14.3k views

Original Post

Jan 1, 2009 at 12:09 AM

I want to create a function that returns table. Could somebody tell me how to write the syntax?


DELIMITER $$ 
DROP FUNCTION IF EXISTS `test`.`Get2` $$ 
CREATE FUNCTION `test`.`Get2` () RETURNS myTable TABLE 
( 
index2 int, 
) 

AS 
BEGIN 

INSERT myTable 
SELECT `index` FROM horsenpc WHERE `level`=9; 

return myTable; 


END $$ 

DELIMITER ;

NBlaine

Jan 5, 2009 at 8:06 AM

Just run a select statement and return that:

RETURN (SELECT field1, field2 FROM Table)

akira32

Author

akira32

Jan 6, 2009 at 7:06 AM

Can MySQL' function return a table?

Wan

154

Wan

Jan 6, 2009 at 7:25 AM

Quote:
Original post by akira32
Can MySQL' function return a table?

Although I'm more familiar with MSSQL where you can't, I don't think it's possible in MySQL to return a table either. And to be honest, it's no really what functions are for.

Where do you need this data exactly? Doesn't generating (not 'returning') a recordset from a procedure suffice?

NBlaine

Jan 6, 2009 at 2:26 PM

Quote:
Original post by WanMaster
Quote:
Original post by akira32
Can MySQL' function return a table?

Although I'm more familiar with MSSQL where you can't, I don't think it's possible in MySQL to return a table either. And to be honest, it's no really what functions are for.

Where do you need this data exactly? Doesn't generating (not 'returning') a recordset from a procedure suffice?

SQL Server does allow a function to return a table. Example

Wan

154

Wan

Jan 7, 2009 at 6:32 AM

Quote:
Original post by NBlaine
Quote:
Original post by WanMaster
Quote:
Original post by akira32
Can MySQL' function return a table?

Although I'm more familiar with MSSQL where you can't, I don't think it's possible in MySQL to return a table either. And to be honest, it's no really what functions are for.

Where do you need this data exactly? Doesn't generating (not 'returning') a recordset from a procedure suffice?

SQL Server does allow a function to return a table. Example

Well, I stand correct then. But from quickly skimming the article it doesn't seem to support it out of the box (as in the old fashioned 2000 version functions)? I no longer work with relational databases, so I might not be fully up to date on this.

akira32

Author

akira32

Jan 7, 2009 at 6:58 AM

Quote:
Original post by WanMaster
Quote:
Original post by akira32
Can MySQL' function return a table?

Although I'm more familiar with MSSQL where you can't, I don't think it's possible in MySQL to return a table either. And to be honest, it's no really what functions are for.

Where do you need this data exactly? Doesn't generating (not 'returning') a recordset from a procedure suffice?

I have a requiremnt to get the records and union them. But the stored procedure cannot union the records I search by select commands. So I choice to use stored function. But MySQL's function cannot return a table. My requirement is to select the records by the complex logic rule and get the result in MySQL++ by VC++. I want to get the result by one stored function replace by the C++ code(find out all records and sieve them).

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.