Log bad call to stored procedure

Question

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.

Expecting : EXEC @Ret = spStoredProc NULL

Getting : 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.

Answer 1

SQLServer does not support function overloading - so you can't write two definitions for this procedure using different argument lists.

However, you could re-write the stored procedure to accept a string rather than a datetime as its first parameter.

I suspect the error occurs because of an implicit type casting when the string 'NULL' is received. You did, however, say that the parameter may be passed as "a string containing a valid date". The fact you get the error when the contents of the string is not a valid date implies to me the function is expecting a datetime (and not expecting a string) and performing an implicit type conversion (because in both those cases, it is receiving a string - in one case where the value represents a valid date, and in the other case where the value is 'NULL' - the first converts the type successfully to datetime and the second fails).

If you explicitly change your procedure to receive a string argument, then perform the conversion within the procedure itself, you could first check if the string value is 'NULL' - then set a local variable as null to be used within the procedure. If the string value is not 'NULL' then use a try catch block to cast(@input as datetime) - if it succeeds, then great, continue with your procedure. If not, throw an error.