error in user defined function "Select statements included within a function cannot return data to a client."

Question

Thanks for your attention and time.

I am declaring a user defiend function which will return a multistatment table.

I need to select categories and based on that selection I will put values in returning table but when I write select statement I

faced the error  "Select statements included within a function cannot return data to a client."

Can you please guide me how I can do this snario.

Best Regards,

haansi

Answer 1

Here's a fab article that you should read on UDFs: http://www.sqlteam.com/article/user-defined-functions


Multi statement functions require you to declare the return table structure in the "header" and complete the function with a single RETURN command (see article for further clarification)

---

George

Answer 2

You will need to use a stored proc instead. Here is the BOL link: http://msdn.microsoft.com/en-us/library/ms186755.aspx. Here is a decent link that explains how to use a stored proc to get around the problem: http://www.sql-server-helper.com/error-messages/msg-444.aspx.

---

JP
Please click the Mark as Answer button if a post solves your problem!
http://www.johnvpetersen.com

Answer 3

Thanks for reference.

I already has looked these links but I thing I couldn't explain my issue. I want to know what should I do in this snario ?

Can you suggest something plz.

Answer 4

Thanks for reference.

I already has looked these links but I thing I couldn't explain my issue. I want to know what should I do in this snario ?

Can you suggest something plz.

Answer 5

Please post the code you're currently working with

---

George

Answer 6

If you want to use a multi-line function, you have to specify a table def in the the header and use a single sql statement to fill that structure. If you need more than one sql statement, then use a stored procedure. You cannot have a sql statement that returns data to the client within a function. That is different from a stored proc. The reason is because a function is a specific data type - whether a singleton or a specfic table structure.

So...if you use a stored proc...

insert inot @mytable
   exec myproc...

something like that..

Then, you can use the results of the stored proc for subsequent operations.

---

JP
Please click the Mark as Answer button if a post solves your problem!
http://www.johnvpetersen.com

Answer 7

Thanks for replaying. Code is as under.

Please let me discrib a little more.

Categories table is self referenced and categories can be nested to any depth. In mediafiles table I have record of mediafiles with categoryid from categories table. My requirment is I want to pass a categoryid and in return funtion should return me a table contaning files of given category and all category that has this category as parent category and all child categories in any depth.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Alter FUNCTION GetFilesInaHeraricy (@BasicId int)
RETURNS @MyFiles table
(
Id int,
Name nvarchar(200),
Discription nvarchar(500),
Language nvarchar(50),
ReleaseDate datetime,
Duration nvarchar(50),
DownloadAble  bit,
CategoryId int,
ArtistId int,
CategoryName nvarchar(100),
ArtistName nvarchar(50)
)
AS
Begin
declare @Sum int;
select @Sum=count(parentcategoryid)  from categories where parentcategoryid=@BasicId;
if(@Sum >0)
    begin
        select * from categories c where parentcategoryid=@BasicId;

    end

Insert @MyFiles
Select m.id,c.categoryName,m.Discription,m.Language,m.ReleaseDate,m.Duration,m.DownloadAble,
m.CategoryId,m.ArtistId,c.categoryname,m.name
from categories c,Artists a, Mediafiles m
where m.artistId=a.id and m.categoryid=c.categoryid;


RETURN
end

Answer 8

I thing It can be done like by declarign a function and using it as funtion recorsion.

Data and table structure of tables is as under.

Categories

CategoryId    int    Unchecked
CategoryName    nvarchar(100)    Unchecked
CategoryDiscription    text    Unchecked
ParentCategoryId    int    Checked
CreatedBy    int    Checked
Visible    bit    Checked

Data

1	Haqooq Ullah	Lectures of different scholers	NULL	1	True
2	Haqooq Ulibaad	Speeches of different scholars	NULL	1	True
23	Ibaadat	Material On ibaadat	1	1	True
24	Faraaiz	Our collection on Faraaiz	23	1	True
25	Namaaz	Material on Namaz	24	1	True
26	Farz Namaaz	All about faraz namaaz	25	1	True
27	Nawafil	All about Nawafil	25	1	True
28	Zakat	Details about Zakat	23	1	True
29	Roza	Stuff on Roza	23	1	True
30	Farz Roza	Stuff on Farz Roza	29	1	True
31	Nafli Roza	Stuff on Nafli Roza	29	1	True
32	Talawat	Talawat	NULL	1	True

MediaFiles

Id    int    Unchecked
Name    nvarchar(200)    Unchecked
Discription    nvarchar(500)    Checked
Language    nvarchar(50)    Unchecked
ReleaseDate    datetime    Checked
Duration    nvarchar(50)    Checked
DownloadAble    bit    Checked
Visible    bit    Unchecked
CategoryId    int    Unchecked
ArtistId    int    Unchecked
OnlyForMembers    bit    Checked
SearchKeywords    nvarchar(500)    Checked

Data

50	Family.jpg	This article is about haqooq of family.	Urdu	8/1/2009 12:00:00 AM	60 Min	True	True	2	1	False	haqooq ulibaad, family
51	Farz Namaz.jpg	It is about importacne of Farz Namaz.	Urdu	8/2/2009 12:00:00 AM	30 Min	True	True	26	2	False	ibaadat, faraiz, namazain
52	Farz Rozay.jpg	Dr. Zakir tells importance of Roza in life.	Urdu	8/3/2009 12:00:00 AM	50 Min	True	True	30	11	False	Roza, Farain, Ibaadat
53	Ibadaat.jpg	S. Ghulam Muhaud Din Shah tells importance of Ibaadat in Islam	Urdu	8/5/2009 12:00:00 AM	40 Min	True	True	23	10	False	Ibaadat
54	Nafli Rozay.jpg	It is about Nafli Roza.	Urud	7/6/2009 12:00:00 AM	100 Min	True	True	31	2	False	Nafli rozay.
55	NafliNamaz.jpg	Get understanding of Nawafil	Urdu	6/8/2009 12:00:00 AM	45 min	True	True	27	9	False	nawafil, ibdaat
56	Rights of Children.jpg	Rights of children on parents.	Urdu	5/18/2009 12:00:00 AM	35 Min	True	True	2	11	False	Family, children, haqooq ulibaad
57	Zakat.jpg	Why Zakat is farz ? Imrotance of Zakat.	Urdu	5/26/2009 12:00:00 AM	40 Min	True	True	28	1	False	zakat, ibaadaat
58	AlNass.mp3	Sura Al-Naas	Arabic	8/4/2009 12:00:00 AM	1 Min	True	True	32	10	False	Al-Naas
59	AlFalaq.mp3	Al-Falaq	Arabic	7/30/2009 12:00:00 AM	2 Min	True	True	32	1	False