| SQL adaptation guide For SQL SERVER 2005, 2008, 2012 / BDL programming | |
The database driver for Microsoft™ SQL SERVER is based on ODBC. The ODBC driver implementation provided with SQL SERVER uses system stored procedures to prepare and execute SQL statements (You can see this with the Profiler).
Some Transact-SQL statements like SET DATEFORMAT have a local execution context effect (for example, when executed in a stored procedure, it is reset to the previous values when procedure execution is finished).
To support such statements in BDL programs, the database driver uses the SQLExecDirect() ODBC API function when the SQL statement is not a SELECT, INSERT, UPDATE or DELETE. This way the SET statement is executed 'directly', without using the system stored procedures. The result is that the SET statement has the expected effect (i.e. a permanent effect).
However, if the SQL statement uses parameters, the ODBC driver forces the use of system stored procedures to execute the statement.
See the MSDN for more details about system stored procedures used by Microsoft APIs.