Select statements included within a function cannot return data to a client_New

Question

I've been reading about the error on the question title but I cannot figure out what is the error on my code below:

CREATE function [RPT].[MonthlyRollupDates_Delimited]()
RETURNS @table TABLE (Value varchar(max))
AS
BEGIN

Declare @Count int , @Date date = getdate(),@Month int
SET @Month = MONTH(@Date)
SET  @Count = @Month +12


Select 
top (@Count) 
CalendarDate,   BusinessDay, Label
from  MonthlyRollupDates
order by Calendardate desc

return
end
GO

The error I'm getting is

Select statements included within a function cannot return data to a client

Hobson

Which dbms are you using? (MS SQL Server?) – Inchworm

I'm using Microsoft SQL Server Management Studio – Hobson

Answer 1

You need to put the results of your SELECTinto your return table, and then return that table.

CREATE function [RPT].[MonthlyRollupDates_Delimited]()
--notice i modified the table to match the select statement, but assumed datatypes
RETURNS @table TABLE (CalendarDate datetime, BusinessDay int, Label char(10))
AS
BEGIN

declare @Count int
SET @Count = month(getdate()) + 12

--place your select into the table variable
insert into @table
Select top (@Count) 
    CalendarDate
    ,BusinessDay
    ,Label
from  
    MonthlyRollupDates
order by 
    Calendardate desc

return
end
GO

Unarmed

I did not know that I needed to put the column names and data types after the "Return @table" , I'll review the correct datatype based on the table where is getting them and update the script if needed. Thank you , I'll mark this question as resolve as soon as I test it :) – Hobson

I'm sorry , I was able to test it a few minutes ago , there are too many business projects and university homework's :( .. Thank you :) – Hobson