Lesson of the day: PRINT in T-SQL has side effects

Not strictly PHP this one, but worth mentioning to the people that use SQL Server from PHP. This is an example based on a stored procedure that I spent too much time debugging today:

   SELECT * FROM FOO WHERE FOOID = @FOOID
   PRINT @FOOID
   IF @@ROWCOUNT = 0
       RAISERROR('BAD')

This always fails--why? The PRINT statement resets the @@ROWCOUNT variable back to 0. Nasty eh?

On a related note, PRINT generates 'SUCCESS_WITH_INFO' diagnostic records (one for each print call), so you can capture the output over ODBC. A gotcha here is that, with SQL Server at least, once you start pulling diagnostic records, you must pull them all, otherwise you block your database connection, leading to invalid cursor state errors when you try to fetch.

Double bad day for PRINT in SQL Server for me.