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.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. ;)|||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?|||//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
*/|||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.|||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 ?|||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.|||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 .|||sorry
I have no idea|||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 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=ZYw1fKKpGI&isbn=020103669X&itm=1) and try to adopt its principles as best you can.

-PatP|||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!"|||"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.|||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 !!!!!.|||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