我想给一个标识符数组作为存储过程的参数.
存储过程如下所示：
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或者我错过了什么？
