A stored procedure is a set of one or more SQL statements that are stored together in database. To create a stored procedure use CREATE PROCEDURE statement. To use the stored procedure send a request for it to be executed. When server recieves the request, it executes the stored procedure.
When creating, remember the followings…..
1. Stored procedure is one or more SQL statements that have been compiled and stored with database.
2. In addition to SELECT statement, a stored procedure can contain other SQL statements such as INSERT,UPDATE,DELETE.
3. Stored procedure can improve database performance because the SQL statements in each procedure are only compiled and optimized the first time they are executed.
4. A trigger is a special type of procedure that executes when rows are inserted, updated or deleted from table.
5. A user defined function(UDF) is a special type of procedure that can return a value or a table.
For Example. First create database from SQL Server Management studio in Administrative mode. Now Expand Microsoft SQL server 2005 database folder > expand database folder > Right click on Tables folder and click on the New Table option > and design database structure as per given fields or as ur requirement
Now create Procedure….
set ansi_nulls on
set quoted_identifier on (optional)
create procedure <procedurename>
select empid, empname, empdoj, empdob
from <tablename> where Name_emp = <something> order by empid
CAUTION: Be care of the syntax.
Now execute this thru Sql Server Management Studio > Execute Stored Procedure and select the procedure name !
For more info: please link here (though it is based on 2008, but still it helps to clear ur concept) http://msdn.microsoft.com/en-us/library/ms345415.aspx