存储过程分不带参数的存储过程和带参数的存储过程:
一、不带参数的存储过程
/*首先从系统数据表sysobjects中查询是否存在名为stu_cj的存储过程,如果存在,删除*/
If exists( select name from sysobjects Where name = 'wo' and type = 'p')
Drop procedure wo
go
/*创建存储过程*/
Create procedure wo
As
select distinct count(职工号) as '职工个数'from worker
Go
/*执行存储过程*/
Exec wo
结果如下:
二、带参数的存储过程
/*首先从系统数据表sysobjects中查询是否存在名为stu_cj的存储过程,如果存在,删除*/
If exists( select name from sysobjects Where name = 'stu_cj' and type = 'p')
Drop procedure stu_cj
go
/*创建存储过程*/
Create procedure stu_cj
@sex char(2) = '女',@de char(1) --变量@sex默认值为'女'
As
Select w.姓名,d.部门名,s.工资
From worker w,salary s,depart d
Where w.职工号 = s.职工号
and w.部门号 = d.部门号
and w.性别 = @sex
and d.部门号 = @de
Go
/*执行存储过程*/
Exec stu_cj @sex='男',@de=2
Go
Exec stu_cj @de=3
结果如下: