Creating a Stored Procedure

Syntax:: CREATE PROC [ EDURE ] procedure_name [ ;number ]
    [ { @parameter data_type }
       [ VARYING ] [ =default ] [ OUTPUT ]
    ] [ ,... ]
[ WITH
    {
       RECOMPILE
       | ENCRYPTION
       | RECOMPILE, ENCRYPTION
    }
]
[ FOR REPLICATION ]
AS
    sql_statement
procedure_name: Is the name of the stored procedure to be created.
number: Is an optional parameter, which is used to group procedures of the same name so they can be dropped using a single DROP PROCEDURE statement.
@parameter: Is a parameter in the procedure. There can be one or more parameters.
data_type: Is the data type of the given parameter.
VARYING: Specifies the result-set supported as an output. Applies only to the cursor parameters.
default: Is a default value for the parameter.
OUTPUT: Indicates that the given parameter is a return parameter.
RECOMPILE: RECOMPILE indicates that SQL Server does not keep the cache for the procedure and it is recompiled each time when it is executed.
ENCRYPTION: ENCRYPTION indicates that the SQL will prevent the procedure from being published as part of SQL Server replication.
FOR REPLICATION: This is used to specify that stored procedures created for replication cannot be executed on the subscribing server and is executed only during replication.
AS: The keyword used just before the SQL statements in the procedure.
sql_statement: Is the SQL statement that is to be executed in the procedure.

The CREATE PROCEDURE (or CREATE PROC) statement is used to create a stored procedure.

Examples


				CREATE PROCEDURE spDisplayAll

				AS 

				SELECT * FROM Students

				GO


				The command(s) completed successfully.

In the above example, we have created a stored procedure spDisplayAll that fetches all of the records in the Students table.