存储过程的定义、描述就不多说了,都明白。直接从基本的存储过和开始讲起。
1. 创建存储过程
CREATE
{
PROC
|
PROCEDURE
}
[
schema_name.
]
procedure_name
[
; number
]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,n ]
[ WITH <procedure_option> [ ,n ] -- procedure_option可以有RECOMPILE、ENCRYPTION等。
[ FOR REPLICATION ]
AS
{ < sql_statement > [ ; ][ n ] | < method_specifier > } [ ; ]
< sql_statement > :: = { [ BEGIN ] statements [ END ] }
< method_specifier > :: = EXTERNAL NAME assembly_name.class_name.method_name
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,n ]
[ WITH <procedure_option> [ ,n ] -- procedure_option可以有RECOMPILE、ENCRYPTION等。
[ FOR REPLICATION ]
AS
{ < sql_statement > [ ; ][ n ] | < method_specifier > } [ ; ]
< sql_statement > :: = { [ BEGIN ] statements [ END ] }
< method_specifier > :: = EXTERNAL NAME assembly_name.class_name.method_name
以上,重要的,procedure_name表示存储过程名,注意不要使用sp_开头的名称,因为容易与系统存储过程混淆。
@parameter表示参数,可以有多个,data_type,表示参数的类型,另外,支持默认参数,只要在参数后面加上"=defaultvalue"即可。
如果想要输出参数,则需要加OUTPUT关键字,
参数定义好后,就开始到正式存储过程的代码了,使用AS表示存储过程代码开始。
sql_statement表示代码正文,以begin..end包括起来。
1.1 一个简单的存储过程
Create
Procedure
prGetEquipment
@chvMake varchar ( 50 )
as
Select * from Equipment where Make = @chvMake
@chvMake varchar ( 50 )
as
Select * from Equipment where Make = @chvMake
1.2 判断存储过程存不存在
if
exists
(
select
*
from
sysobjects
where
id
=
object_id
(
'
prGetEquipment
'
)
and OBJECTPROPERTY (id, ' IsProcedure ' ) = 1 )
drop procedure prGetEquipment
and OBJECTPROPERTY (id, ' IsProcedure ' ) = 1 )
drop procedure prGetEquipment
注:暂时先把这篇给贴上去,以后在项目中用到存储过程时,会把相应的代码和注释贴上来,存储过程一节纯粹是在项目中学习。