Select statements included within a function cannot return data to a client_New
12,884
You need to put the results of your SELECT
into 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
Author by
Samayoa
Updated on June 05, 2022Comments
-
Samayoa about 2 years
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
-
jarlh over 5 yearsWhich dbms are you using? (MS SQL Server?)
-
Samayoa over 5 yearsI'm using Microsoft SQL Server Management Studio
-
-
Samayoa over 5 yearsI 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 :)
-
Samayoa over 5 yearsI'm sorry , I was able to test it a few minutes ago , there are too many business projects and university homework's :( .. Thank you :)