ALTER PROCEDURE

Modify a previously created stored procedure.

Syntax
      ALTER PROC[EDURE] [schema.]procedure  
         [ { @parameter [schema.]data_type } 
             [VARYING ] [ = default ] [ OUT[PUT] ]
         ] [ ,...n ] 
         [WITH Option [,...n ]]
      AS { sql_statement [;][ ...n ] | EXTERNAL NAME assembly.class.method }
      [;]

   Options: 
      ENCRYPTION
      RECOMPILE
      EXECUTE_AS_Clause
      [FOR REPLICATION]

   sql_statement 
      [BEGIN] statements [END]

Key
   @parameter   A local parameter in the procedure.
   VARYING      The result set contents may vary: a cursor parameter dynamically constructed by the procedure. 
   default      A default value for the parameter. A constant or NULL
   OUTPUT       Indicates an output parameter.
   RECOMPILE    Do not cache a plan for this procedure - compile at run time.
   ENCRYPTION   Encrypt the text of the CREATE PROCEDURE statement.
   FOR REPLICATION Execute only during replication.
   EXTERNAL NAME   Reference to a method of a .NET Framework assembly.

Example

ALTER PROCEDURE Sales.GetPartsOfType
@PartCode nvarchar(50)
AS
SELECT PartCode, Description
FROM Sales.parts
WHERE PartCode = @PartCode;
GO

"Love is not love
Which alters when it alteration finds" ~ Shakespeare (Sonnet 116)

Related commands:

CREATE PROCEDURE
EXEC - Execute procedure
DROP PROCEDURE
sys.sql_modules - view procedure definition
Equivalent Oracle command: ALTER PROCEDURE


© Copyright SS64.com 1999-2014
Some rights reserved