Basics of Stored Procedures in SQL Server 2008

Take the following statement:

Select * from customers

This is a plain Transact-Structured Query Language (T-SQL) statement.

If you put that as,

Create procedure usp_myprocedure
as
select * from customers

It becomes a stored procedure. It is that simple. But, it gets quite complicated and has lot of uses when used extensively.

A simple definition of a stored procedure can be "A compiled set of T-SQL statements".

Some Benefits:
1. Stored procedures when registered, basically after you run the create procedure command, actually gets registered at the server.
2. They have input and output parameters that helps to take in some values and give out some other values. For example,

Create procedure usp_insertproc
@name varchar(100),
@ID int,
@print_output varchar(100) OUT
as
Begin
insert into customers (Customer_id,customer_name) values (@id, @name);
set @print_output = 'The insert was successful';
end

3. Same like any programming language, it can be created once and called from multiple places - like another stored procedure or any other application. This helps in modularity.

4. Very easy to execute. As simple as:

Exec usp_myprocedure

This also helps in reduced network traffic. Instead of sending hundreds lines of SQL code, we are sending only one.

5. Stored Procedures has good security attibutes. For example, you can give permission to a user to stored procedure but still not permission to the object that it is accessing.

6. Stored procedures are precompiled, so the same execution plan can be used when called multiple times. This makes it faster than just running a bunch of T-SQL statements.

Types of Stored procedures:

1. System Stored Procedures: They are created by SQL server itself. They are prefixed by sp_ . They can be used for lot of administrative tasks. Examples include: sp_rename; sp_who; sp_configure.

2. User-defined Stored procedures: These are the most used ones, that are created by users. Like the usp_myprocedure or the usp_insertproc examples from above.
2.a. T-SQL: If the user-defined stored procedure is created by T-SQL statements, then it is called T-SQL User-defined stored procedure.
2.b. CLR: If the user-defined stored procedure is a reference to Common Language Runtime (CLR) in .NET, then it is called CLR User-defined stored procedure.

3. Extended stored procedures: If you want to create procedures in languages such as C and use it in stored procedure, you use extended stored procedure. This is deprecated in SQL Server 2008 -- meaning it will not be available in the next version of SQL Server. So, Microsoft suggests using CLR user-defined stored procedure instead.