Table type parameter in a stored procedure cause operand type clash error

Question

I want to give an array of identifiers as argument to a stored procedure.

The stored procedure looks like :

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...
    )

The user table type looks like :

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

When I call the stored procedure from a script in SSMS (I originally have the same problem calling it from .NET code) :

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

I got the error :

Operand type clash: int is incompatible with IntegerList

Answer 1

I found the answer : it was the order of the table type parameter that caused the error !

The table type parameter must be the first in the stored procedure parameters AND ALSO in the arguments passed to the stored procedure call !

The stored procedure :

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...
    )

And the call :

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

A sql server bug or am I missing something ?

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 - location of table type parameter does not matter, you have to make sure sequence while passing data, you can check above code which is working fine where table type parameter is at the last.