access return value from stored procedure in ASP

Can you show me some code sample?

What database are you using?

Sql server

Also, any chance we'd be able to see the CREATE PROCEDURE code for the MoveInitsToDaidsOar sproc?

USE [RIMS]
GO
/****** Object:  StoredProcedure [RIMS].[MoveInitsToDaidsOar]    Script Date: 03/12/2009 14:25:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [RIMS].[MoveInitsToDaidsOar]
(
@INIT_ID INT,
@HIERARCHY_NAME VARCHAR(50)
)

AS
BEGIN
SET NOCOUNT ON

DECLARE

@MEM_ID INT,
@MEM_NAME VARCHAR(50),
@MEM_DESC VARCHAR(50),
@MEM_TYPE VARCHAR(50),
@MEM_ORDER INT,
@MEM_ORDER_TREE INT,
@SET_ID INT,
@MEM_PID INT,
@GROUP_ID INT,
@FY INT,
@MECH VARCHAR(5),
@RIMS_ID INT,
@UNIQUE_NAME VARCHAR(24),
@DIM_ID INT,
@CHECKOUTDATE VARCHAR(24),
@CHECKINDATE VARCHAR(24)

--SET @MEM_PID=15910
--SET @SET_ID=41
--SET @GROUP_ID=42

      IF EXISTS
      (SELECT DIM_ID
      FROM RIMS_A.RIMS_A.DIMENSIONS
      WHERE DIM_CHECKOUT_BY_A IS NOT NULL)
      BEGIN
         --SET @ERROR=1
         --SET @error_desc='Structure checked out.'  
            RETURN(1)
      END

      IF EXISTS
      (SELECT AGGR_OBJECT_ID
      FROM RIMS_A.RIMS_A.AGGR_OBJECTS
      WHERE AGGR_OBJECT_CHECKEDOUT_BY IS NOT NULL)
      BEGIN  
         --SET @ERROR=1
         --SET @error_desc='Structure checked out.'
         RETURN(1)
      END

      IF EXISTS
      (SELECT DIM_ID
      FROM RIMS_A.RIMS_A.DIMENSIONS
      WHERE DIM_CHECKOUT_DATE_A>DIM_RELEASED_DATE_A)
      BEGIN
         --SET @ERROR=1
         --SET @ERROR_DESC='Structure not released. Please release structure.'
         RETURN(1)
      END

      IF EXISTS
      (SELECT AGGR_OBJECT_ID
      FROM RIMS_A.RIMS_A.AGGR_OBJECTS
      WHERE AGGR_OBJECT_CHECKED_OUT_DATE>AGGR_OBJECT_RELEASED_DATE)
      BEGIN
         --SET @ERROR=1
         --SET @ERROR_DESC='Structure not released. Please release structure.'
         RETURN(1)
      END

      BEGIN
               BEGIN TRY

                  -- GET SOURCE MEMBER VALUES
                  SELECT @MEM_DESC=MEM_DESC,@GROUP_ID=GROUP_ID,@MEM_TYPE=MEM_TYPE
                  FROM RIMS.RIMS.CUST2
                  WHERE MEM_ID=@INIT_ID

                  SELECT @FY=FY,@MECH=MECH,@RIMS_ID=RIMS_ID
                  FROM RIMS.RIMS.MISSION_RIMS_MAP
                  WHERE INITIATIVE_ID=@INIT_ID                  

                  -- GET HIERARCHY ID
                  SELECT @SET_ID= GROUP_ID
                  FROM RIMS.RIMS.GROUPS
                  WHERE GROUP_NAME=@HIERARCHY_NAME

                  IF @SET_ID IS NOT NULL
                  BEGIN
                  
                        --GET PARENT IDS OF GRANTS,CONTRACTS IN THE HIERARCHY
                        IF @SET_ID=41 --Total Initiatives hierarchy
                        BEGIN
                        IF UPPER(@MECH)='C'
                        SET @MEM_PID=15910
                        ELSE IF UPPER(@MECH)='G'
                        SET @MEM_PID=15908
                        SET @DIM_ID=11
                        END
                  END
                  ELSE
                  BEGIN
                  --SET @ERROR=1
                  --SET @ERROR_DESC='Hierarchy not found.'
                  RETURN(1)
                  END

                  -- GET NEXT MEM_ID
                  SELECT @MEM_ID = ISNULL(MAX(MEM_ID),0) + 1
                  FROM RIMS.RIMS.CUST2 WHERE MEM_ID<80000000 and MEM_ID >=11000

                  -- GET NEXT MEM_ORDER
                  SELECT @MEM_ORDER = MAX(MEM_ORDER)+1
                  FROM RIMS.RIMS.CUST2

                  -- GET NEXT CUST2_TREE.MEM_ORDER
                  SELECT @MEM_ORDER_TREE = MAX(MEM_ORDER)+1
                  FROM RIMS.RIMS.CUST2_TREE WHERE SET_ID=@SET_ID
                  
                  --CREATE MEM_NAME
                  SET @MEM_NAME = CAST(@FY AS varchar(10))+ '-DAIDS OAR-'+ @MECH + '-' + CAST(@MEM_ORDER_TREE AS varchar(10))

                  --CREATE UNIQUE NAME FOR MISSION_RIMS_MAP
                  SET @UNIQUE_NAME = CAST(@DIM_ID AS varchar(10))+ '.'+ CAST(@SET_ID AS varchar(10)) + '.' + CAST(@MEM_ID AS varchar(10))

               END TRY
               BEGIN CATCH
                    --SET @ERROR = @@ERROR;
                    --SET @ERROR_DESC = 'Error assigning parameters.'                   
                    RETURN(1);
               END CATCH
      END

      

---Check Out Dimensions in authorized database

BEGIN TRANSACTION

      select @CHECKOUTDATE = SUBSTRING(CONVERT ( varchar(20) , getdate() , 121),1,19);

    BEGIN
       BEGIN TRY
            UPDATE      RIMS_A.RIMS_A.DIMENSIONS
            SET            DIM_CHECKOUT_DATE_A=@CHECKOUTDATE,
              DIM_CHECKOUT_BY_A='RIMS',
              DIM_CHECKOUT_TYPE_A='A'

             UPDATE RIMS_A.RIMS_A.AGGR_OBJECTS
            SET
                  AGGR_OBJECT_CHECKED_OUT_DATE=@CHECKOUTDATE,
                  AGGR_OBJECT_CHECKEDOUT_BY='RIMS',
                  AGGR_OBJECT_CHECKEDOUT_TYPE='A'
                  WHERE AGGR_OBJECT_CODE in ('FCR','JRN','COM')

       END TRY
       BEGIN CATCH
          --SET @ERROR = @@ERROR;
          --SET @ERROR_DESC = 'Error checking out structure.'
          ROLLBACK TRANSACTION;
          RETURN(1)
       END CATCH
    END  
            

-- INSERT NEW INITIATIVES INTO AUTHORIZED DATEBASE. WHEN DONE, CHECKIN.

      BEGIN
       BEGIN TRY

            INSERT INTO RIMS_A.RIMS_A.CUST2(MEM_ID,MEM_NAME,MEM_DESC,MEM_TYPE,MEM_ORDER_A,GROUP_ID_A,MEM_DEFINED_BY_A,MEM_UPDATED_A)
              VALUES(@MEM_ID,@MEM_NAME,@MEM_DESC,@MEM_TYPE,@MEM_ORDER,@GROUP_ID,'C','N')

              INSERT INTO RIMS_A.RIMS_A.CUST2_X_SET(MEM_ID,SET_ID)
              VALUES(@MEM_ID,@SET_ID)

              INSERT INTO RIMS_A.RIMS_A.CUST2_TREE(MEM_ID,MEM_PID,SET_ID,MEM_ORDER,MEM_CONSOLIDATE)
              VALUES(@MEM_ID,@MEM_PID,@SET_ID,@MEM_ORDER_TREE,'Y')
            
       END TRY
       BEGIN CATCH
          --SET @ERROR = @@ERROR;
          --SET @ERROR_DESC = 'Error inserting initiatives (Cust2) in authorized database.'
          ROLLBACK TRANSACTION
          RETURN(2)
       END CATCH
    END      

--CHECK IN

select @CHECKINDATE = SUBSTRING(CONVERT ( varchar(20) , getdate() , 121),1,19);

      BEGIN
       BEGIN TRY

          UPDATE RIMS_A.RIMS_A.DIMENSIONS
            SET DIM_CHECKOUT_DATE_A=NULL,
          DIM_CHECKOUT_BY_A=NULL,
          DIM_CHECKOUT_TYPE_A=NULL,
          DIM_UPDATED_DATE_A=@CHECKINDATE

              UPDATE RIMS_A.RIMS_A.AGGR_OBJECTS
            SET
            AGGR_OBJECT_CHECKED_OUT_DATE=NULL,
            AGGR_OBJECT_CHECKEDOUT_BY=NULL,
            AGGR_OBJECT_CHECKEDOUT_TYPE=NULL,
            AGGR_OBJECT_UPDATED_DATE=@CHECKINDATE
            WHERE AGGR_OBJECT_CODE in ('FCR','JRN','COM');

       END TRY
       BEGIN CATCH
          --SET @ERROR = @@ERROR;
          --SET @ERROR_DESC = 'Error checking in structure.';
          ROLLBACK TRANSACTION;
          RETURN(3)
       END CATCH
    END

      

--UPDATE PRODUCTION DATABASE. WHEN DONE, RELEASE.

    BEGIN
       BEGIN TRY

            INSERT INTO RIMS.RIMS.CUST2(MEM_ID,MEM_NAME,MEM_DESC,MEM_TYPE,MEM_ORDER,GROUP_ID)
              VALUES(@MEM_ID,@MEM_NAME,@MEM_DESC,'R',@MEM_ORDER,@GROUP_ID)

             INSERT INTO RIMS.RIMS.CUST2_X_SET(MEM_ID,SET_ID)
            VALUES(@MEM_ID,@SET_ID)

            INSERT INTO RIMS.RIMS.CUST2_TREE(MEM_ID,MEM_PID,SET_ID,MEM_ORDER,MEM_CONSOLIDATE)
            VALUES(@MEM_ID,@MEM_PID,@SET_ID,@MEM_ORDER,'Y')
            
       END TRY
       BEGIN CATCH
          --SET @ERROR = @@ERROR;
          --SET @ERROR_DESC = 'Error inserting initiatives (Cust2) in production database.';
          ROLLBACK TRANSACTION;
          RETURN(4)
       END CATCH
    END    

      -- INSERT INTO MISSION_RIMS_MAP TABLE
      BEGIN
       BEGIN TRY              
            INSERT INTO RIMS.RIMS.MISSION_RIMS_MAP(INITIATIVE_ID,RIMS_ID,UNIQUENAME,DIVISION,VIEW_TYPE,FY,MECH)
            VALUES(@MEM_ID,@RIMS_ID,@UNIQUE_NAME,'DAIDS-OAR','DAIDS OAR',@FY,@MECH)
       END TRY
       BEGIN CATCH
          --SET @ERROR = @@ERROR;
          --SET @ERROR_DESC = 'Error inserting initiatives (mission_rims_map) in production database.'
          ROLLBACK TRANSACTION;
          RETURN(5)
       END CATCH
    END

COMMIT TRANSACTION
END

Take a look here:

http://www.simple-talk.com/sql/t-sql-programming/sql-server-error-handling-workbench/

The idea is that if you get a valid record set you can return that. Otherwise, you can return the error number as a record.


FtB

In short,


Begin
If @@Error <> 0 then
         Select @@Error
Else
         Select .....
End

   

I am returning numbers instead of error numbers in my code.I say Return(1) instead of Return @error.

When I say Return(1) does that mean I am sending 1 in a record.I am not sure.If I am sending that as a record how do you think i can access this number in the  ASP page without using the command object.