I am trying to create a function in SQL Server 2008. The function body is working and debugged. When creating a function, I get the error:

Сообщение 178, уровень 15, состояние 1, процедура WritersAggregateFunc, строка 37 Инструкцию RETURN с возвращаемым значением нельзя использовать в данном контексте. Сообщение 102, уровень 15, состояние 31, процедура WritersAggregateFunc, строка 42 Неправильный синтаксис около конструкции "BEGIN".

What is wrong?

 ALTER FUNCTION [dbo].[WritersAggregateFunc] ( @BookId int ) RETURNS TABLE AS BEGIN SET NOCOUNT ON; DECLARE @result varchar(2000) DECLARE @current varchar(200) SET @result = '' DECLARE cur CURSOR LOCAL READ_ONLY FAST_FORWARD FOR SELECT (l_Writers.Name) FROM l_BooksWriters INNER JOIN l_Writers ON l_BooksWriters.AuthorID = l_Writers.AuthorID WHERE (l_BooksWriters.BookID = @BookID) OPEN cur FETCH NEXT FROM cur INTO @current WHILE @@FETCH_STATUS = 0 BEGIN SET @result = ISNULL(@result + ', ' + @current, @current) FETCH NEXT FROM cur INTO @current END CLOSE cur DEALLOCATE cur SET @result = RIGHT(@result, LEN(@result) - 1) RETURN ( -- Add the SELECT statement with parameter references here SELECT @result ) END

    1 answer 1

    You have incorrect function syntax. Here is a working example from which everything should be clear.

     CREATE FUNCTION TestTableFunc ( @parameter INTEGER ) RETURNS @result TABLE ( ID INTEGER ,Name NVARCHAR(256) ) AS BEGIN -- Разные манипуляции с данными -- Теперь заталкиваем данные в результирующую таблицу INSERT INTO @result (ID, Name) VALUES (1, '1') ,(2, '2'); RETURN; END
    • You're right! Made in the image of your example. - maestro