Using Oracle vs SQL Server procedures to select records for an .asp page.

When using SQL Server as a backend it is often recommended that
developers put their database code into stored procedures (i.e. the
selects, updates, inserts etc...) and then call the stored procedure
from an .asp page using e.g.:

conn.Execute("dbo.MySProc @Username = 'blah'")

where MySProc is something like:

CREATE PROCEDURE dbo.MySProc
@Name NVARCHAR(50) = NULL
AS
BEGIN
SET NOCOUNT ON
SELECT x,y,z,
FROM MyTable
WHERE ColName = @Name
END
GO

The above performs better than doing select from the .asp page:

set rs = conn.execute("select x,y,z from MyTable where ColName =
'blah'")

Does the same apply when using an Oracle database?

Why I ask is that with Oracle you need to use a Ref Cursor to return a
recordset in a stored procedure, so does using a stored procedure in
Oracle have the same benefits as it does in SQL Server?

Re: Using Oracle vs SQL Server procedures to select records for an .asp page.

On 15 Mar 2006 03:22:48 -0800, "Andyza" wrote:

>When using SQL Server as a backend it is often recommended that
>developers put their database code into stored procedures (i.e. the
>selects, updates, inserts etc...) and then call the stored procedure
>from an .asp page using e.g.:
>
>conn.Execute("dbo.MySProc @Username = 'blah'")
>
>where MySProc is something like:
>
>CREATE PROCEDURE dbo.MySProc
> @Name NVARCHAR(50) = NULL
>AS
>BEGIN
> SET NOCOUNT ON
> SELECT x,y,z,
> FROM MyTable
> WHERE ColName = @Name
>END
>GO
>
>The above performs better than doing select from the .asp page:
>
>set rs = conn.execute("select x,y,z from MyTable where ColName =
>'blah'")
>
>Does the same apply when using an Oracle database?
>
>Why I ask is that with Oracle you need to use a Ref Cursor to return a
>recordset in a stored procedure, so does using a stored procedure in
>Oracle have the same benefits as it does in SQL Server?

In almost all instances, Yes...Using the Ref Cursor is just Oracle's way of creating a recordset instead of using the
results of the query in some further processing step ( updating another table for instance, or displaying the results -
Like in SqlPLus)