Ejemplo de definición de proceso en stored procedure

El proceso recibe los parámetros necesarios para bloquear una cuenta

El último parámetro, view, sirve para el proceso le envíe un javascript para que abra la ventana de visualización de la cuenta.

ALTER PROCEDURE [dbo].[pAccount_ChangeState]
@account_id int,
@state_id tinyint,
@lockdate smalldatetime,
@lockreason nvarchar(2000)=null,
@employeechange_id int,
@view bit=0
AS
  declare @trackin_id int
  declare @startdate smalldatetime  
  BEGIN TRY

  BEGIN TRAN  
    
    IF exists (select account_id from ACCOUNT where account_id=@account_id and state_id=@state_id) BEGIN
      RAISERROR('account already had that status.',12,1)
    END
    

    UPDATE ACCOUNT
    SET state_id=@state_id
    ,lockdate= case when @state_id=0 then null else @lockdate end
    ,lockreason=case when @state_id=0 then null else @lockreason end
    ,modifiedby=@employeechange_id, modifieddate=getdate()
    WHERE account_id=@account_id

    IF @view<>0 BEGIN
      SELECT 'flexygo.nav.openPage(''view'',''account'',''(account.account_id=' + CAST(@account_id AS VARCHAR )+ ')'',null,''popup'',true);' AS JAVASCRIPT
    END          
  COMMIT TRAN      
  RETURN -1
END TRY
BEGIN CATCH

  IF @@TRANCOUNT >0 BEGIN
    ROLLBACK TRAN 
  END

  print 'The selected account could not be updated. ' + ERROR_MESSAGE()
  RETURN 0
  
END CATCH

0 Votos