ODBC and SQL Server

Procedures

A stored procedure is a precompiled executable object that contains one or more Microsoft® SQL Server™ statements. Stored procedures can have input and output parameters and can also put out an integer return code. An application can enumerate available stored procedures by using catalog functions.

ODBC applications the target SQL Server should only use direct execution to call a stored procedure. When connected to earlier versions of SQL Server, the SQL Server ODBC driver implements SQLPrepare by creating a temporary stored procedure, which is then called on SQLExecute. It adds overhead to have SQLPrepare create a temporary stored procedure that only calls the target stored procedure versus directly executing the target stored procedure. Even when connected to an instance of SQL Server, preparing a call requires an extra round trip across the network and the building of an execution plan that just calls the stored procedure execution plan.

ODBC applications should use the ODBC CALL syntax when executing a stored procedure. The driver is optimized to use a remote procedure call mechanism to call the procedure when the ODBC CALL syntax is used. This is more efficient than the mechanism used to send a Transact-SQL EXECUTE statement to the server.

For more information, see Running Stored Procedures.

To call remote procedures

ODBC

ODBC

User-defined Functions

User-defined functions in SQL Server 2000 can be executed using the SQL Server 2000 ODBC driver. The syntax for calling user-defined functions using the SQL Server 2000 ODBC driver is similar to that for calling the stored procedures. Whereas stored procedures return only an integer, the user-defined functions can return the value of any SQL Server data type.

See Also

SQLMoreResults

SQLPrepare

User-defined Functions