Инструкция create function должна быть единственной в пакете

I’m getting this error from the function:

CREATE FUNCTION getLavel(@id int ,@lavel char)
RETURNS date
BEGIN
 DECLARE @date date
    select @date = (select authorization_date from Authorized WHERE diver_number = @id and @lavel =level_name)
    return @date
END
GO

What can be the reason?

Ty very much.

juergen d's user avatar

juergen d

202k37 gold badges293 silver badges362 bronze badges

asked Jul 28, 2014 at 19:31

user3885474's user avatar

3

The function needs to be either the only function in the query window OR the only statement in the batch. If there are more statements in the query window, you can make it the only one «in the batch» by surrounding it with GO’s.

e.g.

GO
CREATE FUNCTION getLavel(@id int ,@lavel char)
RETURNS date
BEGIN
 DECLARE @date date
    select @date = (select authorization_date from Authorized WHERE     diver_number = @id and @lavel =level_name)
    return @date
END
GO

answered May 28, 2015 at 8:54

nicV's user avatar

nicVnicV

6507 silver badges16 bronze badges

1

Turn this into an inline table valued function. This will perform better than the scalar function. Also, you should NOT use the default sizes for character datatypes. Do you know what the default length for a char is? Did you know that it can vary based on usage?

CREATE FUNCTION getLavel
(
    @id int
    , @lavel char --You need to define the length instead of the default length
) 
RETURNS table
return 
    select authorization_date 
    from Authorized 
    WHERE diver_number = @id 
        and @lavel = level_name

GO

answered Jul 28, 2014 at 19:38

Sean Lange's user avatar

Sean LangeSean Lange

33.1k3 gold badges25 silver badges40 bronze badges

5

You need to add RETURN before the END statement

That should fix your issue, that’s what fixed mine. :D

answered Sep 22, 2015 at 17:58

FoxArc's user avatar

FoxArcFoxArc

1149 bronze badges

Make sure that this statement is the only the only sql in your query window before you execute it.

Or you can highlight the function declaration and execute

Ben Thul's user avatar

Ben Thul

31.2k4 gold badges45 silver badges68 bronze badges

answered Jul 28, 2014 at 19:35

WantToBeAnonomous's user avatar

What solved it for me, was that I was trying to create the function inside of a transaction context — that doesn’t make sense from a SQL Server point of view. Transactions are for data, not functions.

Take the CREATE FUNCTION statement out of the transaction, then wrap it in GO’s

answered Dec 10, 2017 at 20:05

Michael Malone's user avatar

CREATE FUNCTION CalculateAge(@DOB  DATE) 
RETURNS INT 
AS
BEGIN
DECLARE @Age INT
SET @DOB='08/12/1990'
SET @Age =DATEDIFF(YEAR,@DOB,GETDATE()) -
   CASE
        WHEN (MONTH (@DOB)>  MONTH (GETDATE ())) OR
             (MONTH (@DOB)= MONTH (GETDATE ())  AND DAY (@DOB) >DAY (GETDATE ()))

    THEN 1 
    ELSE 0
    END 
SELECT @Age
END

Tunaki's user avatar

Tunaki

133k46 gold badges342 silver badges424 bronze badges

answered Jun 14, 2016 at 10:38

MR.Rabin Shrestha's user avatar

1

The Error is given to you in only query Page But if you execute the query then it will successfully execute.

CREATE FUNCTION getLavel(@id int ,@lavel char)
RETURNS date
BEGIN
 DECLARE @date date
    select @date = (select authorization_date from Authorized WHERE diver_number = @id and @lavel = level_name)
    return @date
END
GO

answered Oct 25, 2020 at 12:29

Adarsh Bhalani's user avatar

Источник

не знаю почему подчеркивает первую линию красным. В чём может быть проблема. Помогите пожалуйста

BEGIN

CREATE FUNCTION funkcja(@p1 int = 18, @p2 int = 30)
        RETURNS TABLE 
        AS
        RETURN (SELECT * FROM Employees 
                WHERE DATEDIFF(year, BirthDate, getdate()) between @p1 and @p2)
       GO


SELECT EmployeeID, LastName, FirstName, ROW_NUMBER(order by employyID desc) as number 
    FROM funkcja(default) 
    WHERE number  % 3 = 0
    order by desc

END;
GO

введите сюда описание изображения

задан 29 мая 2020 в 20:15

Катеринка's user avatar

1

Вам необходимо убрать ключевое слово BEGIN перед началом создания функции, т.к. это вызывает синтаксическую ошибку: «CREATE FUNCTION» должна быть единственной в пакете

Для вызова каждого параметра по умолчанию, необходимо передать ключевое слово DEFAULT в верхнем регистре

Код должен выглядеть следующим образом

CREATE FUNCTION funkcja(@p1 int = 18, @p2 int = 30)
    RETURNS TABLE 
    AS
    RETURN (SELECT * FROM Employees 
            WHERE DATEDIFF(year, BirthDate, getdate()) between @p1 and @p2)
GO

BEGIN

    SELECT EmployeeID, LastName, FirstName, ROW_NUMBER(order by employyID desc) as number 
    FROM funkcja(DEFAULT, DEFAULT) 
    WHERE number  % 3 = 0
    order by desc

    GO
END;

ответ дан 30 мая 2020 в 10:02

Ilshat Nasyrov's user avatar

Источник

Обновлено

Вопрос:

Я пытаюсь сначала проверить, существует ли функция, и создать ее, если она не существует.

Я получаю эту ошибку от функции:

IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[GetRelativeExpiry]') AND type in (N'U'))
BEGIN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE FUNCTION [dbo].[GetRelativeExpiry]
(
@Date DATE,
@N INT
)
RETURNS DATE
AS
BEGIN
-- Declare the return variable here
DECLARE @Expiry as DATE;

IF @N > 0
BEGIN
SELECT @Expiry = MAX(E2.Expiry)
FROM (SELECT TOP(@N) Expiry
FROM ExpiryDates E1
WHERE E1.Expiry >= @date
ORDER BY E1.Expiry) AS E2
END
ELSE
BEGIN
SELECT @Expiry = MIN(E2.Expiry)
FROM (SELECT TOP(-@N) Expiry
FROM ExpiryDates E1
WHERE E1.Expiry <= @date
ORDER BY E1.Expiry DESC) AS E2
END
RETURN @Expiry

END

END

Я не уверен, почему я получаю эту ошибку, может кто-то может помочь? Я использую Microsoft SQL Server Management Studio 2014

Лучший ответ:

Если вы пытаетесь сделать это в обычном окне в MS SQL Server Management Studio 2014 (или любой предыдущей версии), где вы обычно записываете все другие запросы, то ваше определение функции должно быть очень первым утверждением, которое не комментируется.

См. Изображение ниже. Если инструкция USE PTCRMstaging_test; не был прокомментирован, SQL Server Management Studio даст такую же ошибку.

enter image description here

Ответ №1

Операторы CREATE, будь то для TYPE, PROCEDURE, FUNCTION,... всегда должны быть первым оператором в пакете.

Чтобы обойти это в таких партиях, как ваш, выполните инструкцию CREATE с помощью sp_executesql следующим образом:

EXEC sp_executesql N'
-- your CREATE statement here
';
Источник

Я полагаю, у вас просто плохой код.

Попробуй это:

CREATE FUNCTION GETLLPATH(
    @objectid FLOAT
)
RETURNS VARCHAR(4000)
AS
BEGIN

    DECLARE @dir VARCHAR(MAX);
    DECLARE 
        @obj_id FLOAT
        , @name_NAME VARCHAR(50) -- or whatever your field size is.
        , @name_PARENTID VARCHAR(50)    -- again, whatever your field size is.

    DECLARE Name_Cursor CURSOR LOCAL FOR
    SELECT A.Name, A.ParentID FROM OTCS_User.DTree A WHERE A.DataID = @obj_id;

    SET @dir = NULL; -- redundant.
    SET @obj_id = @objectid;  -- this can be set at declaration ( e.g. DELARE @obj_id FLOAT = @obj_id ).

    WHILE ( 1 = 1 ) BEGIN

        OPEN Name_Cursor;
        FETCH Name_Cursor INTO @name;

        IF ( @@FETCH_STATUS <> 0 OR @name_NAME = 'Enterprise' )
            BREAK;

        IF ( @dir IS NOT NULL ) BEGIN
            SET @dir = (ISNULL(@name_NAME, '') + ':' + isnull(@dir, '')) ;
        END 

        IF @dir IS NULL BEGIN
            SET @dir = @name_NAME;
        END

        SET @obj_id = @name_PARENTID;

        CLOSE Name_Cursor;
        DEALLOCATE Name_Cursor;

    END

    RETURN @dir;

END
GO

Что касается меня, я никогда не любил использовать WHILE (1=1). У вас есть гарантированный выход?

Кроме того, я очень рекомендую использовать альтернативу курсору. Возможно, используйте переменную TABLE и переберите ее так:

CREATE FUNCTION GETLLPATH(
    @objectid FLOAT
)
RETURNS VARCHAR(4000)
AS
BEGIN

    -- declare variables --
    DECLARE @id INT
        , @dir VARCHAR(MAX)
        , @obj_id FLOAT = @objectid
        , @name_NAME VARCHAR(50)
        , @name_PARENTID VARCHAR(50)

    -- declare table variable --
    DECLARE @data TABLE( [name] VARCHAR(50), [parent_id] VARCHAR(50), [id] INT IDENTITY (1,1) );

    -- insert data --
    INSERT INTO @data ( [name], [parent_id] )
    SELECT A.Name, A.ParentID FROM OTCS_User.DTree A WHERE A.DataID = @obj_id;

    -- for-each row... --
    SET @id = 1;
    WHILE ( @id <= ( SELECT MAX( id ) FROM @data ) )
    BEGIN

        -- current row --
        SELECT
            @name_NAME = [name]
            , @name_PARENTID = [parent_id]
        FROM @data WHERE [id] = @id;

        -- do your work here...

        -- next row --
        SET @id = ( @id + 1 );

    END

    RETURN @dir;

END
GO
Источник

SQL Server Error Messages — Msg 111 — ‘CREATE FUNCTION’ must be the first statement in a query batch.

SQL Server Error Messages — Msg 111

Error Message

Server: Msg 111, Level 15, State 1, Line 1
'CREATE FUNCTION' must be the first statement in a query batch.

Causes

As the error message suggests, the CREATE FUNCTION statement must be the first statement in a query batch. There should be no other statements before the CREATION FUNCTION statement that would make it not the first statement in a query batch.

To illustrate, here’s a script that will generate the error message: 

IF EXISTS (SELECT * FROM [dbo].[sysobjects]
           WHERE ID = object_id(N'[dbo].[ufn_IsLeapYear]') AND
                 XTYPE IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[ufn_IsLeapYear]


CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @InputDate    DATETIME )
RETURNS BIT
AS
BEGIN

    IF (YEAR( @InputDate ) % 4 = 0 AND YEAR( @InputDate ) % 100 != 0) OR
        YEAR( @InputDate ) % 400 = 0
        RETURN 1

    RETURN 0

END
GO

Msg 111, Level 15, State 1, Line 7
'CREATE FUNCTION' must be the first statement in a query batch.

When scripting a user-defined function, it is a common practice to check first if the function already exists and if it already exists, drop the function first using the DROP FUNCTION statement before creating it using the CREATE FUNCTION statement. This can also be done using the ALTER FUNCTION statement. The ALTER FUNCTION statement alters an existing Transact-SQL or CLR function that was previously created by executing the CREATE FUNCTION statement, without changing permissions and without affecting any dependent functions, stored procedures or triggers. The only drawback with the ALTER FUNCTION is that it will fail if the function does not exist yet. With the DROP FUNCTION/CREATE FUNCTION combination, the script will always succeed whether the function already exists or not.

As the message suggests, to avoid this error the CREATE FUNCTION statement must be the first statement in a query batch. To make the CREATE FUNCTION statement the first statement in the script above, the GO command can be added to separate the DROP FUNCTION statement from the CREATE FUNCTION statement.

IF EXISTS (SELECT * FROM [dbo].[sysobjects]
           WHERE ID = object_id(N'[dbo].[ufn_IsLeapYear]') AND
                 XTYPE IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[ufn_IsLeapYear]
GO

CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @InputDate    DATETIME )
RETURNS BIT
AS
BEGIN

    IF (YEAR( @InputDate ) % 4 = 0 AND YEAR( @InputDate ) % 100 != 0) OR
        YEAR( @InputDate ) % 400 = 0
        RETURN 1

    RETURN 0

END
GO

The GO command signals the end of a batch of Transact-SQL statements and any statements after the GO command signals the beginning of a new batch of queries or Transact-SQL statements. By adding the GO command after the DROP function statement, the CREATE FUNCTION statement now becomes the first statement in the succeeding query batch.

  • Frequently Asked Questions — SQL Server Error Messages
  • Tips & Tricks — SQL Server Error Messages 1 to 500
Источник

Понравилась статья? Поделить с друзьями:
  • Canon pixma ip4300 инструкция на русском
  • Байкальская межрегиональная природоохранная прокуратура руководство
  • Yamaha psr sx700 инструкция на русском языке скачать
  • Янтарная кислота инструкция для растений отзывы
  • Магний сульфат инструкция по применению уколы внутримышечно инструкция