PDO <-> SQL Server exec (in Laravel) returns empty array

Question

PHP, Laravel, calling

$db->select("EXEC [dbo].[sp_StoredProc1] 1, 3, 1, '2016-06-12 00:00:00', '2016-09-12 00:00:00'");

RESULT - EMPTY ARRAY!!!

Calling the same function via SQL Server Management Studio:

EXEC [dbo].[sp_StoredProc1] 1, 3, 1, '2016-06-12 00:00:00', '2016-09-12 00:00:00'

RESULT - VALID TABLE.

Any idea, why this might happen is warmly appreciated

Do you got `SET NOCOUNT ON;` in your SP? – gofr1 Sep 12 '16 at 14:20 — gofr1, Sep 12 '16 at 14:20

score 3 · Answer 1 · edited May 23 '17 at 12:31

The cause for this - is an ERROR in PHP PDO for MS SQL Server. Exception is not thrown despite happens. The very alike case is depicted here: http://www.sqlservercentral.com/Forums/Topic1754869-392-1.aspx

So it is sort of 2 step problems:

When stored procedure parameters are not EXPLICITLY set to NULL/NOT NULL they are inherited, what causes exception...
which is NOT Shown by PDO (the bug is in Laravel PDO).

Also, you might see this thread PHP - PDOException is not thrown on error, an empty array returned (SQL Server) My colleague did better investigation than me for this issue :)))

score 0 · Answer 2 · answered Sep 12 '16 at 14:20

Try this:

DB::select(DB::raw("EXEC [dbo].[sp_StoredProc1] 1, 3, 1, '2016-06-12 00:00:00', '2016-09-12 00:00:00'");

You can also try to see what you get when you but both the DB::select(...) part and just the DB::raw("...") part in a dd() die and dump function;

PDO <-> SQL Server exec (in Laravel) returns empty array

2 Answers2