Stored Procedures in MS-SQL

Creating a Stored Procedure

You create stored procedures in the SQL Server Management Studio using the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure.

CREATE PROCEDURE StoredProcedureName AS

...

The following code creates a stored procedure called "MyStoredProcedure":

CREATE PROCEDURE MyStoredProcedure AS

SET ROWCOUNT 10

SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice

FROM Products

ORDER BY Products.UnitPrice DESC

Once you run this code in the SQL Server Management Studio, the stored procedure is created and appears under the "Stored Procedures" node.

Modifying a Stored Procedure

If you need to modify an existing stored procedure, you simply replace the CREATE with ALTER.

ALTER PROCEDURE MyStoredProcedure AS

...

more

Running a Stored Procedure

You can run a stored procedure by using EXECUTE or EXEC. For example, to run the above stored procedure, type the following:

EXEC MyStoredProcedure

If the stored procedure has spaces in its name, enclose it between double quotes:

EXEC "My Stored Procedure"

If your stored procedure accepts any parameters, they are placed after the procedure name:

EXEC MyStoredProcedure @ParameterName="MyParameter"

So, here's an example:

EXEC SalesByCategory @CategoryName ="Beverages"

GUI based Approach

You can also use the graphical user interface to initiate the
execution of a stored procedure. 
To initiate a stored procedure this way:
1.    Navigate to the stored procedure 
2. Right click on the stored procedure and select "Execute Stored Procedure...":

3. A dialog will appear. Enter your chosen parameter values etc:

4.   Click "OK"

5. SQL Server will now generate the SQL code and execute the stored procedure:

Parameters
A parameter is a value that your stored procedure uses in order
to perform it's task. When you write a stored procedure, you can specify the
parameters that need to be supplied from the user. For example, if you write a
stored procedure to select the address details about an individual, your stored
procedure needs to know which individual to select. In this case, the user can
provide an IndividualId or UserId to tell the stored procedure which individual
to look up.
System Stored Procedures
SQL Server includes a large number of system stored procedures
to assist in database administration tasks. Many of the tasks you can perform
via SQL Server Management Studio can be done via a system stored procedure. For
example, some of the things you can do with system stored procedures include:
configure   security accounts 
set up linked   servers 
create a  database maintenance plan 
create full  text search catalogs 
configure replication 
set up scheduled jobs 
and much more.