Re-compile Stored Procedures

Re-compile Stored Procedures

Post by Mike A » Sun, 19 Jul 1998 04:00:00



Hi all,  I got some questions, please help.

1.  Do anyone know how to compile a stored procedure using command line ?
2.  If I drop a table, the associated stored procedures will be become
'Invalid', how do I know which s.p. are affected ?

Thanks in advance.

Mike

 
 
 

Re-compile Stored Procedures

Post by Tony Rogerso » Sun, 19 Jul 1998 04:00:00


Quote:>> 1.  Do anyone know how to compile a stored procedure using command line ?

When you create the stored procedure, SQL Server creates a query tree which essentially contains the object id's your using, when
someone calls the sp, if it's not already in cache, SQL Server loads the query tree into the procedure cache, 'compiles' or builds
an execution plan and then executes it.

Basically, SQL Server does it automatically, however, if you want to flush the stored procedure out of cache so that it recompiles
use sp_recompile on a table that the stored procedure accesses.

Quote:>> 2.  If I drop a table, the associated stored procedures will be become
>> 'Invalid', how do I know which s.p. are affected ?

Before dropping the table do a sp_depends on it. There are utilities around to find info like these, perhaps someone else can list a
few.

--
Hope the above helps,

Tony Rogerson
Torver Computer Consultants Ltd.

 
 
 

1. Problem compiling stored procedure that calls another stored procedure

We've been trying to have a stored procedure get results from another
stored procedure but get an error when compiling the calling
procedure:

[IBM][CLI Driver][DB2/LINUX] SQL0104N  An unexpected token "ALLOCATE
rsCur CURSOR FOR" was found following "RE targetProcedure; ".
Expected tokens may include:  "<psm_labellable_stmt>".  LINE
NUMBER=20.  SQLSTATE=42601

Here is an example using the SAMPLE database:

CREATE PROCEDURE DB2INST1.targetProcedure (  )
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

    DECLARE clientcur CURSOR WITH RETURN TO CALLER
          FOR SELECT salary, bonus
          FROM employee;

    OPEN clientcur;
END P1  

CREATE PROCEDURE DB2INST1.callingProcedure (  )
    LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
   DECLARE at_end integer default 0;
   DECLARE not_found CONDITION FOR SQLSTATE '02000';
   DECLARE var1 decimal;
   DECLARE var2 decimal;

   DECLARE result1 RESULT_SET_LOCATOR VARYING;
   DECLARE CONTINUE HANDLER FOR not_found
      SET at_end = 1;

   CALL targetProcedure();
   ASSOCIATE RESULT SET LOCATORS(result1)
      WITH PROCEDURE targetProcedure;
   ALLOCATE rsCur CURSOR FOR result1;
END P1

Thanks,

Daniel

2. Migration/Warehousing Survey!

3. updating statistics and recompiling stored procedures

4. Query Performance Problem SE 5.06.UC1

5. Recompiling stored procedures

6. stored procedure/executable

7. Q:Recompiling Stored Procedures

8. Cannot delete/modify records from SQL Enterprise Manager

9. Difficulty Recompiling stored procedures

10. Recompiling Stored Procedures in MSSQL 6.5

11. Automatically Recompile Stored Procedures

12. Recompiling stored procedure

13. Do I need to recompile stored procedure?