0

I have some problems with running MSSQL store procedure in PDO.

I'm definitely having trouble using a non-parametric store procedure.

I use it like this;

$uid = "sa";
$pwd = "xxx";
try{
  $conn = new PDO ("dblib:host=10.10.10.10:1433;charset=UTF-8;dbname=TESTDB",$uid,$pwd);
}
catch(PDOException $e){
  echo $e->getMessage();
}


$sth = $conn->prepare("exec testSP");
$sth->execute();
while($result = $sth->fetch(PDO::FETCH_ASSOC)) {
var_dump($result);
}

However, when I want to run a store procedure that requires a parameter, there is no result. The codes that do not work are as follows;

$uid = "sa";
$pwd = "xxx";
try{
  $conn = new PDO ("dblib:host=10.10.10.10:1433;charset=UTF-8;dbname=TESTDB",$uid,$pwd);
}
catch(PDOException $e){
  echo $e->getMessage();
}

$param1 = 50;
$sth = $conn->prepare("exec get_next_UniqueNumber ?");
$sth->bindParam(1,$param1);
$sth->execute();
while($result = $sth->fetch(PDO::FETCH_ASSOC)) {
var_dump($result);
}

When I look at the SQL Server Profiler tool, I get a query like this:

exec get_next_UniqueNumber '50'

The store procedure contents are as follows;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[get_next_UniqueNumber]       
@key_lenght int,       
@key nvarchar(50) OUTPUT       
as       
begin       
declare @count int       
select @count= count(*) from unique_numbers where flag = 0       
if (@count < 5)       
begin       
exec sp_generateSemiRandomString       
end        
update unique_numbers set flag=1, @key=unique_number where unique_number = (select top 1 unique_number from unique_numbers WITH (UPDLOCK, HOLDLOCK) where flag =0 order by unique_number)       
SET @key = RIGHT(@key, @key_lenght)          
end

When I run the store procedure with SQL Management Studio, the result is;

DECLARE @return_value int,
        @key nvarchar(50)

EXEC    @return_value = [dbo].[get_next_UniqueNumber]
        @key_lenght = 50,
        @key = @key OUTPUT

SELECT  @key as N'@key'

SELECT  'Return Value' = @return_value

GO
Serdar Karaca
  • 115
  • 1
  • 7
  • MySQL and ms sql server are two different products. Pls only use the relevant product tags! – Shadow Apr 20 '17 at 10:56
  • Also, there is not much point using prepared statements for a query where there are no parameters. – Shadow Apr 20 '17 at 11:00
  • Possible duplicate of [PHP PDO with SQL Server and prepared statements](http://stackoverflow.com/questions/40336368/php-pdo-with-sql-server-and-prepared-statements) – Shadow Apr 20 '17 at 11:01
  • You are calling the stored proc with only 1 parameter in the PDO, but with 2 in SQL Mgmt Studio. – Sloan Thrasher Apr 20 '17 at 11:11
  • I'm not the MSSQL guy at all, never even used it, so I'm just going to comment - I see that you're declaring parameter as integer, but you're supplying the number '50' quoted with single quotes. I don't know if MSSQL will try to cast that or not, but correct approach is to tell PDO *how* to send a parameter if it's not supposed to be treated as string. By default, PDO treats everything as string values. To tell it to send an integer, you should use `$sth->bindParam(1, $param1, PDO::PARAM_INT);`. Now if I'm spouting nonsense, just ignore me :) – Mjh Apr 20 '17 at 11:33

0 Answers0