CREATE PROCEDURE

Create a stored procedure.

Syntax
      CREATE 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.

Examples

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

"What do you do if you step on a mine, Captain? - Well, the usual procedure is to leap 200 feet in the air and spread yourself over a wide area..." ~ Captain Blackadder

Related commands

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


 
Copyright © 1999-2024 SS64.com
Some rights reserved