没有参数的存储过程：

CREATE PROCEDURE 存储过程名称ASSELECT 语句GO

EXECUTE 存储过程名称

或者
EXEC 存储过程名称
以下示例都将使用EXEC。
	带参数的存储过程：
CREATE PROCEDURE my1   @lastname varchar(40),    @firstname varchar(20) AS SELECT * FROM memberWHERE  firstname = @firstname      AND lastname = @lastnameGO

执行方法：
不指名参数将按照声明的参数顺序传入。
EXEC my1 'lastname','firstname'
指名参数：
EXECUTE my1 @lastname = 'last', @firstname = 'first'
或者
EXECUTE my1 @firstname = 'first', @lastname = 'last'
如果在批处理块的第一行，可以直接使用存储过程名称
my1 'lastname','firstname'
或者
my1 @lastname = 'last', @firstname = 'first'
或者
my1 @firstname = 'first', @lastname = 'last'
	创建存储过程时设置参数默认值
CREATE PROCEDURE my1   @lastname varchar(40)='Li',    @firstname varchar(20)='Hui' AS SELECT * FROM memberWHERE  firstname = @firstname      AND lastname = @lastnameGO
执行 EXEC my1相当于EXEC my1 @lastname='Li',@firstname='Hui'
	创建带OUTPUT参数的存储过程

CREATE PROCEDURE my1   @lastname varchar(40),    @firstname varchar(20),   @testoutput varchar(100) OUTPUTAS SELECT @testoutput=email FROM memberWHERE  firstname = @firstname      AND lastname = @lastname and id=1GO
执行
declare @test varchar(100)
exec my1 'Li','Hui',@test OUTPUT
select @test
	使用表值参数
创建一个表值类型：
CREATE TYPE myTableType AS TABLE 
( name VARCHAR(50)
, id INT );
GO
创建带表值参数的存储过程：
create PROCEDURE my1
   @testtabletype myTableType readonly
AS 
SELECT * FROM @testtabletype
GO
	使用OUTPUT游标参数
ALTER PROCEDURE my1   @testcursor cursor VARYING OUTPUTAS SET @testcursor = CURSORFORWARD_ONLY STATIC FORSELECT *FROM memberOPEN @testcursorGO
注意：VARYING指定作为输出参数支持的结果集。该参数由过程动态构造，其内容可能发生改变。仅适用于 cursor 参数。
使用：
DECLARE @MyCursor CURSOREXEC my1 @testcursor = @MyCursor OUTPUTWHILE (@@FETCH_STATUS = 0)BEGIN   FETCH NEXT FROM @MyCursorENDCLOSE @MyCursorDEALLOCATE @MyCursorGO
	使用 WITH RECOMPILE 选项
CREATE PROCEDURE my1   @lastname varchar(40),    @firstname varchar(20) WITH RECOMPILEAS SELECT * FROM memberWHERE  firstname = @firstname      AND lastname = @lastnameGO

WITH RECOMPILE选项将不会把执行计划存入缓存中。

使用WITH ENCRYPTION选项
此选面会对存储过程定义进行加密。当执行exec sp_helptext my1时会提示“对象my1是加密的”，从而不能够查看my1的定义。
执行语句：
SELECT c.id, c.text FROM syscomments c INNER JOIN sysobjects o   ON c.id = o.idWHERE o.name = 'my1'
结果：
id	        text
224719853	NULL


修改存储过程
ALTER PROCEDURE sp_my1   @lastname varchar(40),    @firstname varchar(20) AS SELECT * FROM memberWHERE  firstname = @firstname      AND lastname = @lastnameGO

删除存储过程
DROP PROCEDURE 存储过程名称

存储过程类型
用户定义。用户定义的过程可在用户定义的数据库中创建，或者在除了 Resource 数据库之外的所有系统数据库中创建。
临时。存储于 tempdb 中。本地临时过程的名称以单个数字符号 (#) 开头；它们仅对当前的用户连接是可见的；当用户关闭连接时被删除。全局临时过程的名称以两个数字符号 (##) 开头，创建后对任何用户都是可见的，并且在使用该过程的最后一个会话结束时被删除。
系统。物理上存储在内部隐藏的 Resource 数据库中，但逻辑上出现在每个系统定义数据库和用户定义数据库的 sys 架构中。系统存储过程以sp_开头。

使用 EXECUTE AS 子句，存储过程可用于创建自定义权限集
以下示例使用 EXECUTE AS 为数据库操作创建自定义权限。某些操作（如 TRUNCATE TABLE）没有可授予的权限。通过将 TRUNCATE TABLE 语句合并到存储过程中并指定该过程作为一个有权修改表的用户执行，您可以将截断表的权限扩展至授予其对过程的 EXECUTE 权限的用户。
CREATE PROCEDURE dbo.TruncateMyTableWITH EXECUTE AS SELFAS TRUNCATE TABLE MyDB..MyTable;



附注：存储过程中可以使用批处理控件语句，如IF...ELSE...，TRY...CATCH,事务等。



参考：http://msdn.microsoft.com/zh-cn/library/aa258259(v=sql.80).aspx
http://msdn.microsoft.com/zh-cn/library/ms187926(v=sql.110).aspx