I have an external application making a stored procedure call into a SQL Server 2017 database, via a normal database connection (PHP library (PDO?) of some form using the server, database, user, password) where it sends the stored procedure call as a block of SQL in a text string:
"DECLARE @Ret INT ; EXEC @Ret = spStoredProc :TheDate ; "
where :TheDate is a DATETIME parameter (set to a value via PHP magic), and should be either NULL or a string containing a valid date.
Unfortunately, the caller is passing the NULL as 'NULL' (quoted string), which is leading to a 'Cannot convert varchar to datetime' error during SQL Server's set up to run the stored proc, and the stored procedure never runs.
EXEC @Ret = spStoredProc NULL
EXEC @Ret = spStoredProc 'NULL'
Is there a way to log this situation? I have no control over the external application doing the call, so I want to log this situation on the SQL Server side.