Commit within a store procedure

[inaranjo]

Hi,

Is it valid to perform a commit inside a store procedure?
I have a procedure that performs many selects and updates but it doesn't have a commit. I'm new in sybase, so I don't know if this would cause the procedure to go slower.

How can I do this?

Thanks,

 

[DotNetGnat]

yes you can commit inside a stored procedure...i dont see any problem with it...

look at this sample code found online

CREATE PROCEDURE do_something
    @song_id int, 
    @user_id int, 
    @method int, 
    @length int = 0, 
    @date_exact datetime,
    @default_country int = null
AS  
   -- temporary variables
   DECLARE 
      @artist int, 
      @sample int, 
      @date varchar(32), 
      @country int
BEGIN 
    -- assign temporary variables
    select @date = convert(varchar(32),@date_exact,101) 
    select @artist = user_id, @sample = is_sample from sto_song where song_id = @song_id 
    -- perform conditional code
    if (@sample = 1) begin
        begin transaction
        ... do something ...
        commit transaction
    end else begin
        ... do something else ...
    end
    -- return results
    select 
        result1 = ... some expression ...,
        result2 = ... another expression ...
END

-DNG