locked
function can return multiple resultset RRS feed

  • Question

  • User264732274 posted
    Can function return employee and order details?

    Store proc can do it?
    Tuesday, November 15, 2016 4:33 PM

Answers

  • User475983607 posted

    but i am not sure does the sql server UDF support this or not ?

    Reading the openly published documentation on MSDN indicates that a UDF returns a single result set or variable.  This construct is consistent in most languages.  I'm not sure why you would want to do this in the first place as it is not a "function" type operation.

    What problem are you trying to solve?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 16, 2016 10:55 AM

All replies

  • User-359936451 posted

    You can return any object you want if you are coding with an OOP language. If you are strongly typed then the return set must match what is expected.

    Tuesday, November 15, 2016 4:39 PM
  • User753101303 posted

    Hi,

    And for the SP question see https://support.microsoft.com/en-us/kb/311274 which shows that with ADO.NET. AFAIK it should be exposed as well with higher level APIs such as DataSets or EF...

    Tuesday, November 15, 2016 5:30 PM
  • User264732274 posted

    @Patrice i am asking about store procedure and user define function in sql server. i know store procedure can return multiple result like

    select * from employee

    select * from department

    but i am not sure does the sql server UDF support this or not ?

    Wednesday, November 16, 2016 9:31 AM
  • User475983607 posted

    but i am not sure does the sql server UDF support this or not ?

    Reading the openly published documentation on MSDN indicates that a UDF returns a single result set or variable.  This construct is consistent in most languages.  I'm not sure why you would want to do this in the first place as it is not a "function" type operation.

    What problem are you trying to solve?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 16, 2016 10:55 AM
  • User753101303 posted

    Ah I though it was about C# functions and SQL Stored Proc.

    No, and it's likely because a function that returns a resultset can be reused in another SQL statement while a SP doesn't allow to directly reuse the returned resultset(s).

    Wednesday, November 16, 2016 12:55 PM
  • User-359936451 posted

    You can directly reuse the result set of an SP, if you code it to handle CRUD operations.

    Wednesday, November 16, 2016 3:21 PM