Tuesday, 28 January 2014

The active result for the query contains no fields

Ever had this when calling into SQL Server from PDO in PHP? Well I have. It occurs because of the way in which SQL Server returns multiple results if you are calling a stored procedure with multiple statements in it. For instance, INSERT will return the number of rows affected, as will UPDATE. These are returned to PDO (but stripped out automatically when using SQL Server classes in .Net) which means you code, which looks like this:

$results = $command->queryRow();
$mobileEnabled = $results['mobileenabled'];

Even if your stored procedure appears to returning something like SELECT SCOPE_IDENTITY.

You need to do two things.

1) SET NOCOUNT ON at the top of your stored procedure
2) Ensure you use an alias for the returned value: SELECT SCOPE_IDENTITY() as mobileenabled

This should ensure you get no intermediate results and you can use array syntax to retrieve the value returned.
Post a Comment