1

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.

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.

  • 1
    Thanks. This is pretty much what I thought. I was hoping from some super secret SQL Server internal thingee to handle this. Rather than using a Try/Catch block to handle the date, using TRY_PARSE(@dateparam as DATETIME) would be a lot cleaner. – Evan Nov 27 '18 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.