Msphpsql: SQLSRV - output parameters are not returned from stored procedures with PRINT commands inside

[george-slastnoy]

Just started to migrate my projects from PHP 5.6/Freetds driver to PHP 7.3.8/SQLSRV laters driver (5.6.1), Odbc 17. The SQL Server version is 2008 R2 SP2.

The output parameter variables are not populated, if there are print functions inside an executed stored procedure, although the sqlsrv_query command succeeds.

This causes a serious problem, because it is impossible to debug stored procedures when users call them with the sqlsrv driver. Also, there is a great chance that programmers leave print commands uncommented. Other drivers (freetds, mssql ado and ado.net drivers etc.) discard print messages and return output parameter values correctly.

[yitam]

Please provide a repro script.

Also, did you call sqlsrv_next_result?

[george-slastnoy]

No, but the procedures do not return any result sets (select data). Are print messages treated as result sets?

[george-slastnoy]

What I have figured out is:
1) for numeric parameters: output parameter values are not returned from a stored procedure with print messages if the parameter variable was initialized with the null value. They are returned either if there are no print messages or the parameter variable was initialized with a value other than null and after sqlsrv_query the sqlsrv_next_result function was called.
2) for varchar parameters (tested with a single output parameter): output parameter values are not populated if there are print messages. The sqlsrv_next_result function does not help.

[yitam]

hi @george-slastnoy, please provide a repro script, as I mentioned above. It will help speed things up.

[george-slastnoy]

I have figured it out. I had to put "SET NOCOUNT ON" first after a parameter declaration. Without it, if I use the PRINT command inside a procedure, it interferes with the sqlsrv varchar output parameters. If I put "SET NOCOUNT ON", output varchar parameters are returned correctly.