You can have multiple recorders produced from one procedure.
Here's the equivalent in pl/sql for the example below:
snippet....
create or replace procedure test(
p_username in varchar2,
p_password in varchar2
)
is
v_user_id number;
select user_id
into v_user_id
from users where username =p_username and password = p_password;
EXCEPTION
WHEN NO_DATA_FOUND
select selectthingelse
from sometable where user_id=v_user_id;
end test;
Nick Han
>>> [EMAIL PROTECTED] 03/24/04 12:02PM >>>
On Wed, 2004-03-24 at 11:23, Burns, John D wrote:
> I've done simple stored procedures before to "increase performance"
> (which I already heard today doesn't necessarily do that) but I'm
> wanting to get into using SQL stored procedures for more in depth stuff.
> One thing I'm not sure on how it works is as follows:
>
> CREATE PROCEDURE [dbo].[sm_login]
> @username varchar(40)
> @password varchar(40)
> AS
> select firstname, lastname, userID
> from users
> where username = @username and password = @password
>
> <!----Now, depending on the value (if any) returned from the above query
> I want to grab some other info based on the userID from another table.
> How do I reference the value returned from above? ---->
<cfprocresult
http://livedocs.macromedia.com/coldfusion/5.0/CFML_Reference/Tags77.htm
Do as many selects as you want then specify which result set goes to
which. This supposedly doesn't work in Oracle, but I can not confirm.
>
> In CF, I'd compare it to:
>
> <cfquery...name="query1">
> select firstname, lastname, userID
> from users
> where username = '#form.username#' and password = '#form.password#'
> </cfquery>
>
> <cfquery...name="query2">
> select somethingelse
> from anothertable
> where userID = #query1.userID#
> </cfquery>
>
>
> Is there any way to do something similar in a stored procedure? Thanks!
>
> John Burns
>
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
