存储过程中的表类型参数导致操作数类型冲突错误。

Question

我希望将一个标识符数组作为参数传递给存储过程。

存储过程如下：

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

我遇到了错误：

操作数类型不匹配：int 与 IntegerList 不兼容。

- Dude Pascalou

Answer 1

我找到了答案：问题是表类型参数的顺序导致了错误！

存储过程参数中表类型参数必须是第一个，同时在调用存储过程时传递的参数中也必须是第一个！

存储过程：

ALTER PROCEDURE [dbo].[SearchPerson]
    @siteIds IntegerList READONLY, -- THIS PARAMETER HAS TO BE THE FIRST !
    @personType INT = NULL,
    @city NVARCHAR(64) = 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，还是我漏掉了什么？

- Dude Pascalou

---

要么他们在2008R2中修复了这个问题，要么表类型的定义影响了它的使用。我有效地拥有相同的用户表类型，但“Value”列不是可为空的，并且有一个主键约束。在我的存储过程中，表参数之前和之后都有其他参数。 - Peter

感谢这个 - 虽然在直接的SQL（管理工具）中直接使用带表值参数的存储过程不起作用，但是当我从.NET代码（Entity Framework 5 - System.Data.Entity.Database.SqlQuery）中调用它时遇到了问题，重新排列存储过程中的表值参数+调用代码解决了这个问题！ - James S

@JamesS：我在从.NET代码中调用时也遇到了这个问题。 - Dude Pascalou

Answer 2

  DECLARE @ErrMsg varchar(1000)
  DECLARE @ServiceDates ServiceDatesType

  INSERT @ServiceDates (indexId,unitOfDay,dayOfMonth,dateOfMonth)
  VALUES 
  (0,1,11,'9/11/2016 12:00:00 AM'),
  (1,1,12,'9/12/2016 12:00:00 AM'),
  (2,1,13,'9/13/2016 12:00:00 AM')   

EXEC [usp_SaveValidate] 427,4,12,9,2016,@ErrMsg output,@ServiceDates  
PRINT @ErrMsg
*/

ALTER PROCEDURE [dbo].[usp_SaveValidate] (      
     @EpisodeNo INT 
    ,@ProviderId INT
    ,@ServiceId INT 
    ,@Month INT 
    ,@Year INT          
    ,@ErrorMessage VARCHAR(1000) OUTPUT 
    ,@ServiceDates ServiceDatesType ReadOnly
    )
AS
BEGIN
 -- Code Here
END

SQL SERVER 2012 - 表类型参数的位置并不重要，您只需确保在传递数据时保持顺序。您可以检查上面的代码，其中表类型参数位于最后并且正常工作。

- Dhruv Patel