Microsoft SharePoint Team Blog
The official blog of the Microsoft SharePoint Product Group

Creating a Stored Procedure



To create a procedure:

In the Object Explorer, expand the database for which you want to create the procedure, expand its Programmability node, right-click Stored Procedures, and click New Stored Procedure... A query window with a skeleton syntax would be displayed. You can then modify that code using the techniques we will learn in this lesson.
Open an empty query window associated with the database for which you want to create the stored procedure and display the Templates Explorer. In the Templates Explorer, expand the Store Procedure node. Drag Create Stored Procedure and drop it in the query window
Open an empty query window associated with the database for which you want to create the stored procedure and enter the necessary code
In SQL, to create a procedure, you start with the CREATE PROCEDURE expression. You can also use CREATE PROC. Both expressions produce the same result. Like everything in your database, you must name your procedure:

The name of a procedure can be any string that follows the rules we reviewed for naming the functions
Refrain from starting the name of a procedure with sp_ because it could conflict with some of the stored procedures that already ship with Microsoft SQL Server.
After the name of the procedure, type the keyword AS. The section, group of words, or group of lines after the AS keyword is called the body of the procedure. It states what you want the procedure to do or what you want it to produce.

Based on this, the simplest syntax of creating a procedure is:

CREATE PROCEDURE ProcedureName
AS
Body of the Procedure
It is important to keep in mind that there are many other issues related to creating a procedure but for now, we will consider that syntax.

After creating the procedure, you must store it as an object in your database. To do this, on the SQL Editor toolbar, you can click the Execute button . If the code of the procedure is right, it would be created and a new node for its name would be added to the Stored Procedures section of the database.

Managing Procedures


Modifying a Procedure



As a regular SQL Server database object, you can modify a stored procedure without recreating it. To do this:

In the Object Explorer, you can right-click the procedure and click Modify
In the Object Explorer, you can right-click the procedure, position the mouse on Script Stored Procedure As -> ALTER To -> New Query Editor Window
Open an empty query window associated with the database that contains the stored procedure. From the Templates Explorer, expand Stored Procedure. Drag the Drop Stored Procedure node and drop it in the empty query window
In each case, a skeleton code would be generated for you. You can then edit it to create a new version of your stored procedure. After editing the code, you can execute the SQL statement to update the stored procedure.

In SQL, the basic formula to modify a stored procedure is:

ALTER PROCEDURE ProcedureName
AS
Body of Procedure
Deleting a Procedure



One of the biggest characteristics of a stored procedure is that it is treated like an object in its own right. Therefore, after creating it, if you don't need it anymore, you can get rid of it.

There are various types of stored procedures, some of which are considered temporary. Those types of procedures delete themselves when not needed anymore, such as when the person who created the stored procedure disconnects from the database or shuts down the computer. Otherwise, to delete a procedure, you can use either the Object Explorer or SQL. As mentioned with tables, even if you create a procedure using the Object Explorer, you can delete it using SQL and vice-versa.

To remove a procedure in the Object Explorer, after expanding its database, its Programmability, and its Stored Procedure nodes, you can right-click the stored procedure and click Delete. You can also click it in the Object Explorer to select it and then press Delete. The Delete Object dialog box would come up to let you make a decision.

To delete a procedure in SQL, the syntax to use is:

DROP PROCEDURE ProcedureName
Of course, you should make sure you are in the right database and also that the ProcedureName exists.

Exploring Procedures


Introduction



Probably the simplest procedure you can write would consist of selecting columns from a table. This is done with the SELECT operator and applying the techniques we reviewed for data analysis. For example, to create a stored procedure that would hold a list of students from a table named Students, you would create the procedure as follows:

CREATE PROCEDURE GetStudentIdentification
AS
BEGIN
SELECT FirstName, LastName, DateOfBirth, Gender
FROM Students
END
GO
Besides SELECT operations, in a stored procedure, you can perform any of the database operations we have applied so far. These include creating and maintaining records, etc.

Practical Learning: Creating a Stored Procedure



Make sure you have the Yugo National Bank database created in the Lesson13. If you didn't create it, do it now.
In the Object Explorer, right-click YugoNationalBank and click New Query
To start a stored procedure, type the following:
USE YugoNationalBank;
GO
-- =============================================
-- Author: FunctionX
-- Create date: Friday, May 25, 2007
-- Description: This stored procedure assigns a
-- default password to each employee.
-- =============================================

CREATE PROCEDURE AssignDefaultPassword
AS
BEGIN
UPDATE dbo.Employees
SET Password = 'Password1' FROM dbo.Employees;
END
GO


To create the stored procedure, press F5
Executing a Procedure



To get the results of creating a stored procedure, you must execute it (in other words, to use a stored procedure, you must call it). To execute a procedure, you use the EXECUTE keyword followed by the name of the procedure. Although there are some other issues related to executing a procedure, for now, we will consider that the simplest syntax to call a procedure is:

EXECUTE ProcedureName
Alternatively, instead of EXECUTE, you can use the EXEC keyword:

EXEC ProcedureName
For example, if you have a procedure named GetStudentIdentification, to execute it, you would type:

EXECUTE GetStudentIdentification
You can also precede the name of the procedure with its schema, such as dbo. Here is an example:

EXECUTE dbo.GetStudentIdentification;
You can also precede the name of the schema with the name of the database. Here is an example:

EXECUTE ROSH.dbo.GetStudentIdentification;
Practical Learning: Executing a Stored Procedure



Delete the contents of the query window and replace it with the following:
EXECUTE AssignDefaultPassword;
GO


To execute the stored procedure, press F5
Using Expressions and Functions



One of the advantages of using a stored procedure is that not only can it produce the same expression as we saw during analysis but also it can store such an expression to be recalled any time without having to re-write it (the expression). Based on this, you can create an expression that combines a first and a last name to produce and store a full name. Here is an example:

CREATE PROCEDURE GetStudentIdentification
AS
BEGIN
SELECT FullName = FirstName + ' ' + LastName,
DateOfBirth, Gender
FROM Students
END
GO
A stored procedure can also call a function in its body. To do this, follow the same rules we reviewed for calling functions during data analysis. Here is an example of a stored procedure that calls a function:

USE ROSH;
GO
CREATE PROCEDURE GetStudentsAges
AS
BEGIN
SELECT FullName = FirstName + ' ' + LastName,
DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
Gender
FROM Students
END
GO


source : http://blogs.msdn.com/sharepoint/default.aspx