Microsoft® SQL Server 2012 Unleashed

Creating Stored Procedures

To create a stored procedure, you need to give the procedure a unique name within the schema and then write the sequence of SQL statements to be executed within the procedure. Following is the basic syntax for creating stored procedures:

Click here to view code image

CREATE { PROC | PROCEDURE } [schema_name.]procedure_name    [ { @parameter [ schema_name.]data_type }        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]    ] [ ,...n ][ WITH  {  [ ENCRYPTION ]         , [ RECOMPILE ]         , [ EXECUTE_AS_Clause ]         [ ,...n] ][ FOR REPLICATION ]AS[BEGIN]    SQL_Statements[   RETURN scalar_expression ][END]

It is good programming practice to always end a procedure with the RETURN statement and to specify ...