SAP Cursor Cache for MS SQL Server

Purpose

For MS SQL Server, the process is different from the process for Oracle.

For MS SQL Server, DBSL transforms the statement ID to a stored procedure name. The stored procedure with this name is generated in the database. The set of stored procedures is shared by all SAP work processes.

If the statement does not have a statement ID (for example, a dynamic statement), the DBSL uses a temporary statement cache. Each entry in the temporary statement cache holds the statement string and its associated temporary stored procedure's name. Currently, each SAP work process owns a set of temporary stored procedures.

Process

Executing Normal SQL Statements

A normal SQL statement is a statement with statement ID (that may or may not be normalized by the Statement Analyzer):

DBSL creates the stored procedure name from the statement ID.
Executes the stored procedure.
If it works, the process is finished.
If it does not work, DBSL creates the stored procedure in the database and then executes it.

Executing Dynamic SQL Statements

A dynamic SQL statement is a statement without statement ID (such as a dynamic statement that is specified completely at runtime):

DBSL searches the temporary stored procedure cache to find the SQL statement and the name of the associated temporary procedure.
If it is found, the temporary stored procedure will be executed. The process is now finished.
If it is not found, the temporary stored procedure is generated in the database and executed. The statement string and the new temporary stored procedure's name are inserted into the temporary statement cache. This entry then maps to the temporary stored procedure.