Oracle always requires that cursors be used with SELECT statements, regardless of the number of rows requested from the database. In Microsoft SQL Server, a SELECT statement that is not enclosed within a cursor returns rows to the client as a default result set. This is an efficient way to return data to a client application.
SQL Server provides two interfaces for cursor functions. When cursors are used in Transact-SQL batches or stored procedures, SQL statements can be used to declare, open, and fetch from cursors as well as positioned updates and deletes. When cursors from a DB-Library, ODBC, or OLEDB program are used, the SQL Server client libraries transparently call built-in server functions to handle cursors more efficiently.
When porting a PL/SQL procedure from Oracle, first determine whether cursors are needed to do the same function in Transact-SQL. If the cursor returns only a set of rows to the client application, use a noncursor SELECT statement in Transact-SQL to return a default result set. If the cursor is used to load data a row at a time into local procedure variables, you must use cursors in Transact-SQL.