Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    12

    Unanswered: Table-Valued Function error

    Hi everybody,
    I wrote a table-valued function like below, but unfortunately I encountered with this error
    "Select statements included within a function cannot return data to a client",please help me.

    CREATE FUNCTION fn_Mediafile_Path(@MediaID int)
    RETURNS @Table Table
    (
    ID int,
    Existence int,
    Fullpath nvarchar(max),
    Name nvarchar(1000),
    Description nvarchar(max),
    TVNetworkID int,
    StorageID int,
    MediaID int,
    MediaName nvarchar(1000),
    FileKindCode int,
    FormatCode int,
    VFrameRate int,
    ASampleRate int,
    ABitsPerSample int,
    AChannelCount int,
    Duration int,
    Filesize int,
    Height int,
    Width int,
    Filename nvarchar(max),--dbo.fn_FileExistance(dbo.fns_path_corrector(path.P ath)+ f.Filename)
    ParentID int,
    VCodecCode int,
    VCodecName int,
    ACodecCode int,
    ACodecName int,
    RequestCount int,
    ProgramID int,
    ProgramName nvarchar(1000),
    BackupCount int,
    Code nvarchar(1000)
    )
    AS
    Begin

    Declare @ID int,@ID1 int,@MfID int,@TotalExist int=0,@Exist int
    Declare @t Table(MediaID int)

    Declare c cursor
    For Select ID From media Where ID=@MediaID
    Open c
    Fetch Next From c Into @ID
    While @@Fetch_Status != -1
    Begin
    Insert Into @t
    Select ID From v_Mediafile
    Where mediaID=@ID


    Select mf.ID,Sum(Existence) AS Existence,(Select Top 1 Fullpath From v_Mediafile
    Where ID=mf.ID And Existence != 0) AS FullPath,

    mf.Name, mf.Description, mf.TVNetworkID, mf.StorageID, mf.MediaID, mf.MediaName,
    mf.FileKindCode, mf.FormatCode,mf.VFrameRate,mf.ASampleRate, mf.ABitsPerSample, mf.AChannelCount,
    mf.Duration, mf.Filesize, mf.Height, mf.Width,
    mf.Filename,--dbo.fn_FileExistance(dbo.fns_path_corrector(path.P ath)+ f.Filename)
    mf.ParentID, mf.VCodecCode, mf.VCodecName, mf.ACodecCode, mf.ACodecName,
    mf.RequestCount,mf.ProgramID,mf.ProgramName,mf.Bac kupCount,mf.Code

    From v_Mediafile mf Where ID In (Select MediaID from @t )

    Group by mf.ID,mf.Name, mf.Description, mf.TVNetworkID, mf.StorageID, mf.MediaID, mf.MediaName,
    mf.FileKindCode, mf.FormatCode,mf.VFrameRate,mf.ASampleRate, mf.ABitsPerSample, mf.AChannelCount,
    mf.Duration, mf.Filesize, mf.Height, mf.Width,
    mf.Filename,--dbo.fn_FileExistance(dbo.fns_path_corrector(path.P ath)+ f.Filename)
    mf.ParentID, mf.VCodecCode, mf.VCodecName, mf.ACodecCode, mf.ACodecName,
    mf.RequestCount,mf.ProgramID,mf.ProgramName,mf.Bac kupCount,mf.Code

    Fetch Next From c Into @ID

    End

    Close c
    Deallocate c
    Return
    End

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You have to insert the records you want to return into the table variable (@Table in your example)

    The cursor is likely going to give you significant performance problems. Are you by chance from an Oracle background?

  3. #3
    Join Date
    Aug 2011
    Posts
    12

    Thx

    Thank you, it works

Posting Permissions

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