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

Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial