Passing parameters to SQL Server stored procedures with PHP PDO

Question

I'm having trouble passing parameters to a stored procedure on SQL Server from PHP with PDO.

I can successfully perform a query on a procedure that requires no parameters, with code like this simplified version:

$dbConnection =  Craft::createComponent(array(
    'charset'           => 'utf8',
    'class'             => 'CDbConnection',
    'autoConnect'       => true,
));

$dbConnection->connectionString = 'sqlsrv:Server=MYSERVER;Database=My_Database';
$dbConnection->username = 'MyUsername';
$dbConnection->password = 'MyPasword';

$command = $dbConnection->createCommand(
    'MYSERVER.My_Database.My_Schema.my_Stored_Procedure'
);

$data = $command->queryAll();

This returns data fine.

But if the stored procedure requires parameters, I can't work out how to do that. I've tried replacing the createCommand line with something like:

$command = $dbConnection->createCommand(
    "MYSERVER.My_Database.My_Schema.my_Stored_Procedure 1, 'A Parameter', '', ''"
);

using the four parameters I've been given, that should work, but this gets me:

 CDbCommand failed to execute the SQL statement: SQLSTATE[IMSSP]: The active
 result for the query contains no fields.. The SQL statement executed was:
 MYSERVER.My_Database.My_Schema.my_Stored_Procedure 1, 'A Parameter', '', ''

I'm not familiar with the syntax for SQL Server stored procedures, never mind calling them from PHP, so I'm a bit stumped.

Yes; I've tried binding parameters, but I get the same error message, only with the final part like The SQL statement executed was: MYSERVER.My_Database.My_Schema.my_Stored_Procedure ?, ?, ?, ? — Phil Gyford
– Phil Gyford, Commented Jul 3, 2015 at 15:53

meda · Accepted Answer · 2015-07-03 20:20:16Z

1

If your stored procedure does not return anything you have to use SET NOCOUNT ON;

You can read more about it here:

https://msdn.microsoft.com/en-us/library/ms189837.aspx

answered Jul 3, 2015 at 20:20

meda

45.5k14 gold badges97 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phil Gyford Over a year ago

You're right! I even had a similar issue a day earlier and completely forgot about that being a possible solution. Thanks so much.

Collectives™ on Stack Overflow

Passing parameters to SQL Server stored procedures with PHP PDO

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related