This is just a simple query to test this behavior. Proof of concept.
In reality, there are many cases when NULL has to be passed to a stored procedure or query.
Your original examples used an “=” in the prepared statement… this you cannot do with NULL… Null does not have an equality… it must be used with “IS” or “IS NOT” , or coverted using statements like “isNULL”,“NVL” etc (depending on database engine)
Actually no, thats the point of coalesce … it returns the first non null value from the list of parameters
select COALESCE( null ,‘Hi’) would return ‘Hi’
That said, I have no idea what select myvar = COALESCE( null ,‘Hi’) was supposed to do
Maybe return one row with TRUE or FALSE depending upon the contents of myvar?
But Myvar is not predefined here or selected from a table…
So that test SQL just makes no sense to me, but hey, maybe its doing something too cool for me to follow…
[quote=323993:@Stan F]
BTW, differences from what I see you do on SQLite and SQL Server:
passing parameters on SQL Server works only through Bind(), passing directly in SQLExecute() or SQLSelect() doesn’t work,
recordset is returned from SQLSelect() only, SQLExecute doesn’t return a recordset.
S.[/quote]
SQLExecute is for anything that does NOT return a set of rows as a result
SQLSelect is for anything that returns results as rows (even a stored procedure that returns a set of rows)
All problems have disappeared when I switched to the ODBC driver.
ODBCPreparedStatement.ODBC_TYPE_NULL works as expected and allows me to pass Nils/Nulls to SQL Server.
Hi Stan,
I am just trying to pass NULL as a Parameter to Sqlserver for a DateTime column using ODBC.
However it does not work whatever I try. I did manage to pass NULL to a numeric column though.
Did you get it to work such datatypes?