CODEWITHSUNDEEP
phpsql-serverstored-procedurespdoexecute
guppster01
guppster01

Reputation: 1

Calling MSSQL Stored Procedure using PDO not working

Trying to Call a MSSQL Stored Procedure. Procedure is basically this: USE [DATABASE] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER procedure [dbo].[STOREDPROCEDURE] (@username varchar(10)) as

SELECT * FROM 'table name' where userfield = @username

PHP Code
$username = 'username';
try {
    $connection = new PDO('odbc:Driver={SQL Server Native Client 10.0};Server={SERVER};Database={DATABASE};Uid={USER};Pwd={PASSWORD}', 'USER', 'PASSWORD');
}
catch(PDOException $e) {
    echo 'Connection Failed :'.$e->getMessage().PHP_EOL;
    echo 'Please contact the application administrator';
}

$stm = $connection->prepare("CALL STOREDPROCEDURE(?)");
$stm->bindParam(1, $username, PDO::PARAM_STR, 10);
$stm->execute();
$results = $stm->fetchAll();
var_dump($results);

The output is: array(0) { } There is plenty of data in this database.

I know the connection to the database works, when I change the name of the database in the connection it fails.

However, when I change the name of the stored procedure in my (Call ADanE1Username) line, I get no change in the results.

Any help would be appreciated. Thanks.

Upvotes: 0

Views: 4554

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

With SQL SERVER you must use

$stm = $connection->prepare("EXEC STOREDPROCEDURE(?)");

EXECUTE (Transact-SQL)

Upvotes: 2

Related Questions