Calling a Stored Procedure
The Microsoft® SQL Server™ ODBC driver supports both the ODBC CALL escape sequence and the Transact-SQL EXECUTE statement for executing stored procedures; the ODBC CALL escape sequence is the preferred method. Using ODBC syntax enables an application to retrieve the return codes of stored procedures and the SQL Server ODBC driver is also optimized to use a protocol originally developed for sending remote procedure (RPC) calls between SQL Servers. This RPC protocol increases performance by eliminating much of the parameter processing and statement parsing done on the server.
The ODBC CALL escape sequence for calling a procedure is:
{[?=]call procedure_name[([parameter][,[parameter]]...)]}
where procedure_name specifies the name of a procedure and parameter specifies a procedure parameter.
A procedure can have zero or more parameters. It can also return a value (as indicated by the optional parameter marker ?= at the start of the syntax). If a parameter is an input or an input/output parameter, it can be a literal or a parameter marker. If the parameter is an output parameter, it must be a parameter marker because the output is unknown. Parameter markers must be bound with SQLBindParameter before the procedure call statement is executed.
Input and input/output parameters can be omitted from procedure calls. If a procedure is called with parentheses but without any parameters, the driver instructs the data source to use the default value for the first parameter. For example:
{call procedure_name( )}
If the procedure does not have any parameters, the procedure can fail. If a procedure is called without parentheses, the driver does not send any parameter values. For example:
{call procedure_name}
Literals can be specified for input and input/output parameters in procedure calls. For example, the procedure InsertOrder has five input parameters. The following call to InsertOrder omits the first parameter, provides a literal for the second parameter, and uses a parameter marker for the third, fourth, and fifth parameters. (Parameters are numbered ordinally, beginning with a value of 1.)
{call InsertOrder(, 10, ?, ?, ?)}
Note that if a parameter is omitted, the comma delimiting it from other parameters must still appear. If an input or input/output parameter is omitted, the procedure uses the default value of the parameter. Other ways to specify the default value of an input or input/output parameter are to set the value of the length/indicator buffer bound to the parameter to SQL_DEFAULT_PARAM, or to use the DEFAULT keyword.
If an input/output parameter is omitted, or if a literal is supplied for the parameter, the driver discards the output value. Similarly, if the parameter marker for the return value of a procedure is omitted, the driver discards the return value. Finally, if an application specifies a return value parameter for a procedure that does not return a value, the driver sets the value of the length/indicator buffer bound to the parameter to SQL_NULL_DATA.
Delimiters in CALL statements
The Microsoft SQL Server ODBC driver by default also supports a compatibility option specific to the ODBC { CALL } escape sequence. The driver will accept CALL statements with only a single set of double quotation marks delimiting the entire stored procedure name:
{ CALL "master.dbo.sp_who" }
By default the SQL Server ODBC driver also accepts CALL statements that follow the SQL-92 rules and enclose each identifier in double quotation marks:
{ CALL "master"."dbo"."sp_who" }
When running with the default settings, however, the SQL Server ODBC driver does not support using either form of quoted identifier with identifiers that contain characters not specified as legal in identifiers by the SQL-92 standard. For example, the driver cannot access a stored procedure named "My.Proc" using a CALL statement with quoted identifiers:
{ CALL "MyDB"."MyOwner"."My.Proc" }
This statement is interpreted by the driver as:
{ CALL MyDB.MyOwner.My.Proc }
The server will raise an error that a linked server named MyDB does not exist.
The issue does not exist when using bracketed identifiers, this statement is interpreted correctly:
{ CALL [MyDB].[MyOwner].[My.Table] }
Users needing to access objects with periods in their identifiers can also use the odbccmpt command prompt utility to allow this. The odbccmpt utility supports a /Q switch to enforce ODBC and SQL-92 compliant behaviors of quoted identifiers on the CALL statement. To turn the standard compliant behavior on for an application, use the following code, where file_name is the name of the application executable file without the path or .exe extension.
odbccmpt file_name /Q
For more information, see odbccmpt Utility.
To turn the standard compliance off for an application, use the following code.
odbccmpt file_name /Q /d
Running odbccmpt with only the /Q switch adds this key to the Windows NT registry, running with both /Q and /d deletes the key.
HKEY_LOCAL_MACHINE
SOFTWARE
Microsoft
MSSQLServer
Client
ODBCQIBehavior
file_name:REG_SZ:NEW
When the standard compliance option is on for an application, CALL statements cannot use just a single set of double quotation marks around the complete, qualified procedure name. Only individual identifiers can be quoted. Some examples of valid statements are:
{ CALL "MyDB"."MyUserID"."My.Proc" }
{ CALL "MyDB".MyUserID."My.Proc" }
{ CALL MyDB.MyUserID."My.Proc" }
When the standard compliance option is on, the SQL Server ODBC driver supports using quoted identifiers that contain characters not allowed in SQL-92 identifiers.
To call remote procedures