Regarding the question about returning more than one value from a stored
proc...

I suppose you could do something like this as the last line of a stored
procedure, instead of a return:

	SELECT @value1 as 'Value1', @value1 as 'Value1', ... @valueN as 'ValueN'

The result would be a single record recordset with all the values you want.
You could put the result of @@identity in there along with whatever else you
need.
Unfortunately I am not much help with the rest, as I don't work with
transactions.

Peter

-----Original Message-----
From: martha j sayers [mailto:marthaj@r...]
Sent: Saturday, March 03, 2001 8:21 AM
To: Interdev_Programming
Subject: STORED PROCS


I have a question.
I am using InterDev with Sql Server 2000.
I am using stored procedures.

What I want to know is using begin transaction, commit transaction and so
forth sinse this will be a multi-user situation.

Where do I insert the begin transaction statement etc ??
How does it work in mult-user mode ??

I know with recordsets I can set the type of locking. But what about when

I execute the stored proc in my asp page ??

DO I make it part of the stored procedure ??
 (I am not returning a error code, I am returning the indentity field
sinse I need to have the indentity to return to me.)

Can I return more than one value from the stored procedure ??

This is asp code:


    set rsMast = Server.CreateObject("ADODB.Connection")
    rsMast.Open vdbase
    rsMast.Errors.Clear
    set rsCmmds = Server.CreateObject("ADODB.Command")
    set rsCmmds.ActiveConnection = rsMast
    rsCmmds.CommandText = "ins_new_car"
    rsCmmds.CommandType = adcmdStoredProc
    rsCmmds.Parameters.Append rsCmmds.CreateParameter
("RC",adInteger,adParamReturnValue)
    rsCmmds.Parameters.Append rsCmmds.CreateParameter
("BookingId",adInteger,adParamInput,4,vbookid)
    rsCmmds.Parameters.Append rsCmmds.CreateParameter
("CustomerId",adInteger,adParamInput,4,vcustid)
    rsCmmds.Parameters.Append rsCmmds.CreateParameter
("AgencyId",adInteger,adParamInput,4,vagencyid)
    rsCmmds.Parameters.Append rsCmmds.CreateParameter
("AgentId",adInteger,adParamInput,4,vagentid)
    rsCmmds.Parameters.Append rsCmmds.CreateParameter
("AgentName",adVarChar,adParamInput,65,vagentname)
    rsCmmds.Parameters.Append rsCmmds.CreateParameter
("BookAgencyId",adInteger,adParamInput,4,vbookagency)
    rsCmmds.Parameters.Append rsCmmds.CreateParameter
("Refund",adVarChar,adParamInput,1,vrefund)
    rsCmmds.Parameters.Append rsCmmds.CreateParameter
("RecStatus",adVarChar,adParamInput,100,vcreated)
    rsCmmds.Execute
    if rsMast.Errors.Count = 0  then
       Session("CarId") = rsCmmds.Parameters.Item("RC")
       rsMast.
       set Cmmds = nothing
       set rsMast = nothing
       Response.Redirect "SuppCarSearch.asp"
    else
       txtMessages.value = "CAN NOT ADD RECORD AT THIS TIME. PLEASE TRY
LATER"
  	   set Cmmds = nothing
       set rsMast = nothing
    end if


This is the stored procedure:

CREATE PROCEDURE dbo.ins_new_car
   @BookingId INT,
   @CustomerId INT,
   @AgencyId INT,
   @AgentId INT,
   @AgentName nchar(65),
   @BookAgencyId INT,
   @Refund nchar(1),
   @RecStatus nchar(100) as
Insert Into CarRentals
(BookingId,CustomerId,AgencyId,AgentId,AgentName,BookAgencyId,Refund,RecSta
tus)
       Values
(@BookingId,@CustomerId,@AgencyId,@AgentId,@AgentName,@BookAgencyId,@Refund
,@RecStatus)
RETURN  @@IDENTITY

GO


  I would certainly appericate any help.
Thank you - marthaj