Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Return a result set from a SELECT query in a function?

    Hello all:

    How can I return the result of a SELECT statement from a stored procedure (function)?
    Code:
    CREATE FUNCTION returnAllAuthors ()
    RETURNS (what do i put here??)
    BEGIN
    
    // Is this right??
    RETURN SELECT * FROM authors
    
    END
    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    RETURNS Table

    Check out "Table Valued Functions" in Books Online

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - I'm sure you realise that without input parameters this might as well be a view yah?

  4. #4
    Join Date
    Jan 2008
    Posts
    186
    Is "Returns Table" part of SQL standard? I can't get it to work
    Code:
    CREATE FUNCTION returnAllAuthors ()
    RETURNS table
    BEGIN
    RETURN (SELECT * FROM authors)
    Hmmmm...I'll look into what you said about Views. I'm not too pro with databases so I'm unfamiliar with views, but I'll read some tutorials on them and re-consider.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A view is basically a stored query...
    Code:
    CREATE VIEW authorsStartingWithA
      AS
    SELECT authorName
    FROM   authors
    WHERE  authorName LIKE 'A%'
    Code:
    SELECT * FROM authorsStartingWithA
    Last edited by gvee; 01-22-08 at 06:39.
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Look up CREATE FUNCTION.

    SELECT star?....

  7. #7
    Join Date
    Jan 2008
    Posts
    186
    Is this ability to return a resultset from a function part of the SQL standard? Or is it specific to MS SQL?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeppers (the former).

  9. #9
    Join Date
    Jan 2008
    Posts
    186
    I hate asking this, but could anyone provide an example of how to do this using SQL standard? The only examples i found were DB-specific

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    http://forums.devshed.com/mysql-help...on-503959.html

    only in this case, the guy wants to do it in mysql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dbguyfh
    I hate asking this, but could anyone provide an example of how to do this using SQL standard? The only examples i found were DB-specific
    Ok - is this a SQL Server question or an ANSI SQL question? Coz if the latter then we are in the wrong forum and I have the wrong frame of reference.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't even know if ansi sql supports functions

    in any case i'd want to know

    1. whether it's sql server or mysql (same guy in the other forum site, different dbms)

    2. exactly how he plans to use this table-generating function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I only know from Wikipedia, not first hand knowledge:
    http://en.wikipedia.org/wiki/User_Defined_Function

    Quote Originally Posted by wikipedia
    The SQL standard distinguishes between scalar and table functions.

Posting Permissions

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