PDO::bindParam/bindValue creates the wrong parameter type

Question

I have a stored procedure that takes some integer parameters:

CREATE PROCEDURE [dbo].[redacted_name]
    @CustomerID int , 
    @ContactPersonID int,
    @ChangeContactPersonId int = null,
    @Name varchar(50)  = NULL
    -- snip
AS
BEGIN
  -- snip
END

I'm using the following code to call this stored procedure:

$db = $this->db();
$statement = $db->prepare("exec dbo.redacted_name @CustomerId=?,@ContactPersonId=?");

$statement->bindValue(1, 73, PDO::PARAM_INT);
$statement->bindValue(2, 42, PDO::PARAM_INT);
$statement->execute();
//snip

Which generates the following SQL, grabbed from a profiler:

declare @p1 int
exec sp_prepexec @p1 output,N'@P1 TEXT,@P2 TEXT',N'exec dbo.redacted_name  @customerId=@P1,@contactPersonId=@P2','73','42'
select @p1
exec sp_unprepare @p1
go

Executing this SQL, causes the following error on Microsoft Sql Server 11.0.5343:

Msg 206, Level 16, State 2, Procedure redacted_name , Line 0
Operand type clash: text is incompatible with int

Which makes some sense, because TEXT != INT Considering I specifically told PDO that I'm passing an INT, why does PDO generate two TEXT variables? And what can I do to work around it?

Answer 1

I suggest you to investigate on "driver options" argument for BindParam() maybe it would help.

There are some cases where it may be the problem.