Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Question Unanswered: How To Return Multiple Table Values From A Function

    Hi,
    We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function.

    Create Function F_clusters()
    Returns @ki Table(names Nvarchar(200),total Int),
    As
    Begin
    Insert @ki
    Select Names,count(distinct Chremail) As From Customer
    Where Chremail Is Not Null
    Return
    End

    This Works Fine :-
    And Gives The Reqd. Results.

    But,

    If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk.



    Create Function F_clusters()
    Returns @ki Table(names Nvarchar(200),total Int),@k2 Table(names Nvarchar(200),total Int)
    As
    Begin
    Declare @cnt Int
    Set @cnt = 1
    While @cnt <= 2
    If @cnt =1
    Begin
    Insert @ki
    Select Names,count(distinct Chremail) As From Customer
    Where Chremail Is Not Null
    Set @cnt = @cnt + 1
    End
    If @cnt =2
    Begin
    Insert @k2
    Select @naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B
    On A.intcustomerid = B.intcustomerid
    Where Chremail <> ' ' And Chremail Is Not Null
    And Intpreferenceid In (6,7,2,3,12,10)
    Set @cnt2 = @cnt2 + 1
    End
    End
    Return
    End


    Can We Return Two Tables Or Is It Not Possible ?
    Pls Chk Into This And Tell Me.

    Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't believe it's possible, and if it is then it's not a good idea.

    One of the big advantages of UDF table functions is that you can join them directly into SQL statements.

    select * from my table inner join myfunction on mytable.pk = myfunction.pk

    Now, what would happen to this if myfunction returned more than one data set?

    On a side note, it's nOT nECSSARY tO cAPITALIZE tHE fIRST lETTER oF eACH wORD. In English, just capitalize the first letter of each sentence, and all proper nouns.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2004
    Posts
    49
    HI,

    THANKS FOR THE TIP ON ENGLISH, BUT IS IT POSSIBLE TO GET MORE THAN 1 DATA SET FROM A FUNCTION ... AS I AM REALLY HAVING A PROBLEM WITH IT ?.

    THANKS FOR THE HELP ON FUNCTIONS

    BUT COULD YOU TELL ME ABOUT HOW TO GET DIFFERENT SET OF DATA FROM THE SAME FUNTIONS.


    LETS'S A SIMPLE ONE :-

    Create Function F_clusters()
    Returns TABLE
    AS
    RETURN
    (
    Select count(distinct Chremail) As CNTS From CLEANCustomer
    Where Chremail Is Not Null

    Select count(distinct Chremail) As CNTS From CLEANCustomer
    Where Chremail Is Null

    )

    I WILL GET IT RIGHT IF I GIVE THE FIRST SELECT STATEMENT ONLY

    BUT IF I USE THE SECOND SELECT STATEMENTS IN THE FUNCTION IT WILL NOT EXECUTE.

    SO,IS IT POSSIBLE TO GET TWO DIFFERENT SETS OF DATA FROM A FUNCTION.

    IF YES,HOW ?

    CAN WE DECLARE TWO TABLES WHICH CAN RETURN VALUES ?
    IF YES,HOW?

  4. #4
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    23
    //What about a subselect?
    //The subselect returns two columns, one with the Null count
    //and one with the Not Null count

    Create Function F_clusters()
    Returns TABLE
    AS
    RETURN
    (
    Select count(distinct c1.Chremail) As CNTS
    (Select count(*)
    //The disctinct does not work with a Null Column, its like counting nothing
    From CLEANCustomer As c2
    Where c2.Chremail Is Null) As CNTSNull

    From CLEANCustomer As c1
    Where c1.Chremail Is Not Null
    )

    //A second way is the Union:
    //The Result of the Union are two rows with two fields, one as the count,
    //one as the the type
    Create Function F_clusters()
    Returns TABLE
    AS
    RETURN
    (
    Select count(distinct Chremail) As CNTS,
    'Not Null' As Nulltype
    From CLEANCustomer
    Where Chremail Is Not Null
    UNION
    Select count(*)
    'Not Null'
    From CLEANCustomer
    Where Chremail Is Null
    )

    /*
    This is only SQL, try to take some SQL lessons

    Sneaky Pie
    */

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. You can only return one dataset from a function. Rewrite you code as a stored procedure, which can return multiple datasets:


    Create Procedure F_clusters()
    AS
    Select count(distinct Chremail) As CNTS From CLEANCustomer
    Where Chremail Is Not Null

    Select count(distinct Chremail) As CNTS From CLEANCustomer
    Where Chremail Is Null


    ...but this sort of thing is not much use in application development. You rethink what you are doing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2004
    Posts
    49
    HI,

    THANKS FOR THE HELP BY BOTH OF YOU,BUT STILL FACING THE PROBLEM ,

    THE subselect FUNCTION IS NOT WORKING :-
    I TRIED SEVERAL PERMUTATIONS AND COMBINATIONS

    Create Function F_clusters()
    Returns TABLE
    AS
    RETURN
    (
    Select count(distinct c1.Chremail) As CNTS
    (Select count(*)
    //The disctinct does not work with a Null Column, its like counting nothing
    From CLEANCustomer As c2
    Where c2.Chremail Is Null) As CNTSNull

    From CLEANCustomer As c1
    Where c1.Chremail Is Not Null
    )


    AND YES YOU ARE RIGHT ABOUT USING STORED PROCS TO GET MULTIPLE DATASETS :-

    BUT YOU ARE FORGETTING THOUGH WE HAVE PROCS WHICH GIVES US THE RESULTS , WE ARE TRYING TO CONVERT THEM INTO FUNCTIONS

    AND THEREFORE FACING PROBLEMS WITH MULTIPLE DATASETS,

    FUNCTION CAN RETURN A TABLE , BUT NOT TWO MULTIPLE DATA SETS
    IS THERE A WAYOUT OF IT ?

    OR ELSE WE MIGHT HAVE TO WRITE SEVERAL FUNCTIONS TO REPLICATE WHAT 1 PROC RESULTS WAS REFLECTING WHICH WOULD BE A REAL PAIN.

    IS THERE A WAY OUT ?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are you so hot to convert your stored procs into functions? Functions are not necessarily better than stored procedures.

    I can't shake the feeling that there are underlying problems with your data architecture, application design, or project goals, and unless you resolve these you are not going to be any better off than you were.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2004
    Posts
    49
    Hi,

    We Are Trying To Convert Procs Into Funtions ,so That We Can Invoke Them In Any Other Functions And Use Them As An I/p To Other Procs Or Functions.

    But,if We Are To Use Them As Procs Then We Have The Limitation Of Not Using Insert..exec Statement More Than Once.

    So, There Fore Functions Are Necessary ,but It Now Seems That They Cannot Give Up More Than 1 Datasets ,which Is Again Causing Us Problems.

    Out We Are Trying To Automate The Process.

    Can You Give Something Better Option To Work ?

    As , It Seems We Are Working Round And Round .

  9. #9
    Join Date
    Mar 2004
    Posts
    14

    try 中文

    sorry
    I have no idea

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that the problem lies in the fact that the routine (stored procedure or function) is trying to return more than one result set.

    Any given routine should do exactly one thing. If a routine manipulates data (INSERT/UPDATE/DELETE) it should do that one thing, and nothing more. You may have a few stored procedures that are "wrappers" that call a sequence of other routines, but even they do only one thing, wrap the other routines. Designing your code so that a routine intentionally does a dozen things is a receipe for disaster in my opinion. Any complex thing that works is only a collection of simple things that work!

    Your biggest problem in my opinion is that you need to adopt a more disciplined methodology. It appears that you are used to writing "spaghetti code", and are having a hard time breaking that mindset. Although it is considered "old fashioned" by internet standards, I think the simplest way out of the problems you are facing right now is to embrace "structured programming", although you need to keep in mind that there are many other methodologies that offer even more benefits at the cost of more complexity.

    I'd suggest that you read Software Tools and try to adopt its principles as best you can.

    -PatP

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The definition of a FUNCTION is to return ONE THING. That thing can be a value (scalar) or a table (inline or table-valued). Data connectivity (ODBC API's and SQLOLEDB) for SQL offer the ability to retrieve multiple resultsets from a statement execution against a connection object/handle. This is a flexibility feature, not a standard.

    We all understand that you "HAVE TO CONVERT BLAH-BLAH-BLAH...", but you have to understand that what everybody tries to offer here is tricks and techniques that are based on specific well known rules that IT industry lives by. Complexity and/or brilliance of solutions varies, but the concept remains. Unless in the future there will be a break-through in technology that would alow sending rockets to the moon with a simple SELECT, or returning more than "ONE THING" from a function, - you'll have to learn how to abide by these industry rules.

    And one last thing, - no matter how many more times you choose to post the same question (HOW TO MAKE A FUNCTION RETURN MORE THAN ONE RESULTSET), you will NOT get any more elaborate answers, other than "YOU CAN'T!"
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "We Are Trying To Convert Procs Into Funtions ,so That We Can Invoke Them In Any Other Functions" - A tautological argument

    "... And Use Them As An I/p To Other Procs Or Functions." - And how would you use them as input if they returned more than one result set? The problem here is with your development process, and not due to a limitation of SQL.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Apr 2004
    Posts
    49

    Thumbs up

    Hi,
    Thanks Pat ,BLINDMAN & RDJABAROV FOR THE HELP. and HAS YOU SAID ALL ,I Think I Have To Bring In More Discipline In My Programming
    AND YES MAYBE GET BACK TO STRUCTURED PROGRAMMING

    And Ok !!! Think,it's Time To Break Down Most Of The Routines Into Simple Steps.

    BUT, I STILL HOPED THERE WAS A WAY OUT OF IT !!!!!.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by C_BHANDARY
    BUT, I STILL HOPED THERE WAS A WAY OUT OF IT !!!!!.
    If you find a way out, please publish it. The book will make you a very rich person, and the rest of us very happy people!

    -PatP

Posting Permissions

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