The table type parameter in the stored procedure causes an operand type collision error

Question

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

The stored procedure is as follows:

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 is as follows:

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

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

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

I got an error:

Operand type collision: int 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!

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 ! 

Sql server error or am i missing something?

Answer 2

  DECLARE @ErrMsg varchar(1000) DECLARE @EncounterServiceDates EncounterServiceDatesType INSERT @EncounterServiceDates (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_EncounterSaveValidate] 427,4,12,9,2016,@ErrMsg output,@EncounterServiceDates PRINT @ErrMsg */ ALTER PROCEDURE [dbo].[usp_EncounterSaveValidate] ( @EpisodeNo INT ,@ProviderId INT ,@ServiceId INT ,@Month INT ,@Year INT ,@ErrorMessage VARCHAR(1000) OUTPUT ,@EncounterServiceDates EncounterServiceDatesType ReadOnly ) AS BEGIN -- Code Here END 

SQL SERVER 2012 - the location of the table type parameter does not matter, you need to make sure the sequence when transmitting data, you can check the above code, which works fine when the table type parameter is on the last.