SQL Server Stored Procedures

"SQL Server Stored Procedures
Stored Procedures are SQL statements saved in a database as a n object. They can be called interactively through the Query Analyzer, and by other stored procedures. They can be defined with parameters to make them more flexible, and can return result sets and status codes.

Their advantages include:

The ability to restrict access to the underlying tables;
Increased security in a networked environment;
The vehicle to modularize programming on the database end of an application by allowing re-usable code with callable subroutines;
Faster execution because they are pre-compiled;
Preservation of integrity in tables through enforced data procedures; and
Reduction of programming error and network traffic in passing in ad hoc queries.
Disadvantages (which pale by comparison) include:

Declining portability of code because these are SQL Server specific commands;
Less powerful and flexible programming language as compared to frone-end front-end interface languages;
Sub-par development environment, although with Visual Studio .NET, this facility has been enhanced.
Stored procedures can be created in the Query Analyzer either by using the raw language, templates, or cutting and pasting from the View Creation GUI. . And they can be created in the Enterprise Manager by right-clicking on the StoredProcedure section and choosing New. . The raw syntax is:

CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @ parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ , ... n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ... n ]

Stored Procedures (Sprocs) are executed either by invoking it by name, and or supplying the required parameters (if any). If the procedure is not the first command in a batch, you must precede the procedure name with