sql-server-2008 – 存储过程中的表类型参数导致操作数类型冲突

存储过程如下所示：

ALTER PROCEDURE [dbo].[SearchPerson]
    @personType INT = NULL,@city NVARCHAR(64) = NULL,@siteIds IntegerList READONLY,-- some other params...
AS
    SELECT
        -- some fields...
    FROM dbo.PersonView AS pv
    WHERE
    (
        (@personType IS NULL OR pv.PersonType = @personType) AND
        (@city IS NULL OR pv.City LIKE '%' + @city + '%') AND
        (pv.SiteId in (SELECT si.Value FROM @siteIds AS si)) AND
        -- some other params filter...
    )

用户表类型如下所示：

CREATE TYPE [dbo].[IntegerList] AS TABLE(
    [Value] [int] NULL
)

当我从SSMS中的脚本调用存储过程时(我最初从.NET代码调用它时遇到同样的问题)：

DECLARE @siteIds AS IntegerList,@personType AS INT = 1
INSERT INTO @siteIds VALUES (1)
EXEC [dbo].[SearchPerson] @personType,@siteIds

我收到了错误：

Operand type clash: int is incompatible with IntegerList

解决方法

表类型参数必须是存储过程参数中的第一个参数,并且在传递给存储过程调用的参数中也是ALSO！

存储过程：

ALTER PROCEDURE [dbo].[SearchPerson]
    @siteIds IntegerList READONLY,-- THIS PARAMETER HAS TO BE THE FIRST !
    @personType INT = NULL,-- some other params...
AS
    SELECT
        -- some fields...
    FROM dbo.PersonView AS pv
    WHERE
    (
        (@personType IS NULL OR pv.PersonType = @personType) AND
        (@city IS NULL OR pv.City LIKE '%' + @city + '%') AND
        (pv.SiteId in (SELECT si.Value FROM @siteIds AS si)) AND
        -- some other params filter...
    )

电话：

DECLARE @siteIds AS IntegerList,@personType AS INT = 1
INSERT INTO @siteIds VALUES (1)
EXEC [dbo].[SearchPerson] @siteIds,@personType -- PUT @siteIds FIRST !

一个sql server bug或者我错过了什么？

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!