none
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


    Tuesday, August 18, 2009 12:39 PM

All replies

  • 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
    Tuesday, August 18, 2009 12:44 PM
  • 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
    Tuesday, August 18, 2009 12:46 PM
  • 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.
    Tuesday, August 18, 2009 12:51 PM
  • 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.
    Tuesday, August 18, 2009 12:51 PM
  • Please post the code you're currently working with
    George
    Tuesday, August 18, 2009 12:57 PM
  • 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
    Tuesday, August 18, 2009 12:57 PM
  • 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
    Tuesday, August 18, 2009 1:28 PM
  • 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
    Tuesday, August 18, 2009 1:29 PM