在 SQL 中创建和更改表值函数时不兼容的对象类型

Question

提问by ary

I'm getting the below error for the given function.

对于给定的函数，我收到以下错误。

Msg 2010, Level 16, State 1, Procedure GetTableFromDelimitedValues, Line 2 Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is an incompatible object type.

消息 2010，级别 16，状态 1，过程 GetTableFromDelimitedValues，第 2 行无法对“dbo.GetTableFromDelimitedValues”执行更改，因为它是不兼容的对象类型。

IF NOT EXISTS(SELECT 1 FROM sys.objects 
              WHERE object_id = OBJECT_ID('[GetTableFromDelimitedValues]'))
BEGIN
   EXECUTE('CREATE FUNCTION [dbo].[GetTableFromDelimitedValues](@input varchar(max),
       @delimiter char(1) = ",")) RETURNS @Result TABLE (
       Value nvarchar(4000)) AS BEGIN RETURN END')
END
GO


ALTER FUNCTION [dbo].[GetTableFromDelimitedValues](
       @input varchar(max),
       @delimiter char(1) = ',')
RETURNS @Result TABLE
(
       Value nvarchar(4000)
)
AS
BEGIN
    DECLARE @position int;
    DECLARE @column nvarchar(4000);

    WHILE LEN(@input) > 0
    BEGIN
        SET @position = CHARINDEX(@delimiter, @input);
        IF (@position < 0) OR (@position IS NULL)
        BEGIN
            SET @position = 0;
        END

        IF @position > 0 
        BEGIN
            SET @column = SUBSTRING(@input, 1, @position - 1);
            SET @input = SUBSTRING(@input, @position + 1, LEN(@input) - @position)
        END
        ELSE
        BEGIN
            SET @column = @input;
            SET @input = '';
        END 

        INSERT @Result (Value) 
        SELECT @column;
    END;

    RETURN;                
END
GO

Can someone please help me to get the compatible type by fixing the function?

有人可以通过修复功能来帮助我获得兼容类型吗？

Answer 1

回答by SharK

You need to DROPand CREATEthe function in this particular context

您需要在此特定上下文中删除和创建函数

Since there is change in function return type, we must drop then recreate the function.

由于函数返回类型发生变化，我们必须删除然后重新创建函数。

There are three types of functions,

共有三种功能，

Scalar
Inline table valuedand
Multi Statement

标量
内联表值和
多语句

ALTER cannot be used to change the function type.

ALTER 不能用于更改函数类型。

Answer 2

回答by M.Ali

IF  EXISTS (SELECT [name] FROM sys.objects 
            WHERE object_id = OBJECT_ID('GetTableFromDelimitedValues'))
BEGIN
   DROP FUNCTION [GetTableFromDelimitedValues];
END
GO

/*  Now create function */
CREATE FUNCTION [dbo].[GetTableFromDelimitedValues](
       @input varchar(max),
       @delimiter char(1) = ',')
RETURNS @Result TABLE (
       Value nvarchar(4000)
)
AS
BEGIN
..
..
..
RETURN;
END

in OBJECT_IDfunction you need to pass only function name not the schema. and why would create it 1st and then Alterit . Just check for existence 1st if it exists then drop function and create your function as I have shown above.

在OBJECT_ID函数中，您只需要传递函数名而不是模式。以及为什么要先创建它，然后再创建Alter它。只需检查存在 1 是否存在，然后删除函数并创建您的函数，如我上面所示。

Also do not add Typein where clause when checking for existence, if there is another object not a function but any other object with the same name, it will not pick it up in your select statement and you will end up creating a function with a name an object already exists (this will throw an error).

Type在检查是否存在时也不要在 where 子句中添加，如果有另一个对象不是函数而是任何其他同名的对象，它不会在你的 select 语句中选择它，你最终会创建一个带有名称的函数一个对象已经存在（这将引发错误）。

IF you want to do it your way this is how you would go about it

如果你想按照自己的方式去做，这就是你要做的

IF NOT EXISTS(SELECT 1 FROM sys.objects 
              WHERE object_id = OBJECT_ID('[GetTableFromDelimitedValues]'))
BEGIN
   EXECUTE('CREATE FUNCTION [dbo].[GetTableFromDelimitedValues]() RETURNS @Result TABLE (
       Value nvarchar(4000)) AS BEGIN RETURN END')
END
GO

Answer 3

回答by sesy

In my case, this happened when I have a table name exactly as proc name. so making a change to proc name or a table referred in the proc should also fix this error message.

就我而言，当我有一个与 proc 名称完全相同的表名时，就会发生这种情况。因此，更改 proc 名称或 proc 中引用的表也应该修复此错误消息。

Answer 4

回答by Lefteris Gkinis

I have something to notify on your error related to your code:
The error says Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is an incompatible object type
Which means that you have to look on your lines after the ALTER....
And yes there t is:
@input varchar(max)
The SQL server 2008 r2 not accept objects varchar(MAX), but that is only if you run a stored procedure
Because if you create a table by hand then it is fully accept it.
If you want a large cell then type varchar(1024)or varchar(2048)both of them are accepted. I face this issue few days ago...
That is my humble opinion

我有一些事情要通知您与您的代码相关
的错误：错误说Cannot perform alter on 'dbo.GetTableFromDelimitedValues' because it is an incompatible object type
这意味着您必须在之后查看您的行，ALTER....
是的，有：
@input varchar(max)
The SQL server 2008 r2 not accept objects varchar(MAX)，但只有当您运行时一个存储过程
因为如果你手工创建一个表，那么它是完全接受它的。
如果你想要一个大的单元格，然后输入varchar(1024)或者varchar(2048)它们都被接受。我前几天遇到这个问题......
这是我的拙见

ADDITIONAL CHANGES
Use this

其他更改
使用此

IF NOT EXISTS(SELECT 1 FROM sys.objects 
WHERE object_id = OBJECT_ID('[GetTableFromDelimitedValues]')) 
BEGIN 
execute('CREATE FUNCTION [dbo].[GetTableFromDelimitedValues]( @input varchar(max), @delimiter char(1)= ",") RETURNS @Result TABLE ( Value nvarchar(4000)) AS BEGIN RETURN END')
END GO

.... Pay attention to the change from ' to the "

.... 注意从 ' 到 "

** ADDITIONAL CHANGES **

** 其他更改 **

I use the following which also works fine... with no any issue...

我使用以下也可以正常工作......没有任何问题......

IF  EXISTS (SELECT [name] FROM sys.objects 
            WHERE object_id = OBJECT_ID('GetTableFromDelimitedValues'))
BEGIN
   DROP FUNCTION [GetTableFromDelimitedValues];
END
BEGIN
   execute('CREATE FUNCTION [dbo].[GetTableFromDelimitedValues]() 
    RETURNS 
    @Result TABLE (
    Value nvarchar(4000)) 
    AS 
    BEGIN 
    RETURN 
    END')
    execute('ALTER FUNCTION [dbo].[GetTableFromDelimitedValues](
       @input varchar(max),
       @delimiter char(1) = ",")
       RETURNS @Result TABLE (
       Value nvarchar(4000))
       AS 
    BEGIN 
    RETURN 
    END')
END
GO

Answer 5

回答by ary

I confirm the below code works. Seems the issue was somehow a scalar value function created with the same name during my development and got error as script's multi part table value alter statement function is compatible with it.

我确认以下代码有效。似乎问题是在我的开发过程中以某种方式创建了一个同名的标量值函数，并且由于脚本的多部分表值更改语句函数与它兼容而出错。

IF NOT EXISTS(SELECT 1 FROM sys.objects 
              WHERE object_id = OBJECT_ID('[GetTableFromDelimitedValues]'))
BEGIN
   EXEC sp_executesql 
    @statement = N'CREATE FUNCTION dbo.[GetTableFromDelimitedValues] () RETURNS @Result 
    TABLE(Value nvarchar(4000))
    AS 
    BEGIN 
      RETURN 
    END' ;
END
GO

ALTER FUNCTION [dbo].[GetTableFromDelimitedValues](
    @input varchar(max),
    @delimiter char(1) = ',')
RETURNS @Result TABLE
(
    Value nvarchar(4000)
)
AS
BEGIN
    DECLARE @position int;
    DECLARE @column nvarchar(4000);

    WHILE LEN(@input) > 0
    BEGIN
        SET @position = CHARINDEX(@delimiter, @input);
        IF (@position < 0) OR (@position IS NULL)
        BEGIN
            SET @position = 0;
        END

        IF @position > 0 
        BEGIN
            SET @column = SUBSTRING(@input, 1, @position - 1);
            SET @input = SUBSTRING(@input, @position + 1, LEN(@input) - @position)
        END
        ELSE
        BEGIN
            SET @column = @input;
            SET @input = '';
        END 

        INSERT @Result (Value) 
        SELECT @column;
    END;

    RETURN;                
END
GO

在 SQL 中创建和更改表值函数时不兼容的对象类型

提问by ary

回答by SharK

回答by M.Ali

回答by sesy

回答by Lefteris Gkinis

回答by ary

相关推荐

最近更新

标签

在 SQL 中创建和更改表值函数时不兼容的对象类型

提问by ary

回答by SharK

回答by M.Ali

回答by sesy

回答by Lefteris Gkinis

回答by ary

相关推荐

SQL 如何在sqlite中为dateTime添加时间？

SQL“或”运算符。在以下场景中它是如何工作的？

列出 SQL Server 2012 架构中所有表的名称

如果在 sql 到 linq 中存在语句

相关推荐

最近更新

标签