CREATE PROCEDURE u_analyse2003
@id varchar(1),
@rating integer
AS
DECLARE @sele VARCHAR(8000)
IF UPPER(@id) = 'B'
BEGIN
-- B按职位
DECLARE @job VARCHAR(2)
DECLARE @jobname VARCHAR(16)
SET @sele = 'select left(space(2*(departmentcode.rating))+departmentcode.departmentname,50) as 部门 ,'
DECLARE cur CURSOR FOR
SELECT job,jobname FROM jobcode
OPEN cur
FETCH NEXT FROM cur INTO @job,@jobname
WHILE (@@fetch_status = 0)
BEGIN
SET @sele = @sele +' (select count(*) from person where person.department like departmentcode.department+'+'''%'' AND person.job ='''+@job+''') as '''+ @jobname +''','
FETCH NEXT FROM cur INTO @job,@jobname
END
SET @sele = left(@sele,len(@sele)-1)
SET @sele = @sele +' from departmentcode '
SET @sele = @sele +' where departmentcode.rating <= '+ cast(@rating as varchar(2))
SET @sele = @sele +' order by departmentcode.myorder '
CLOSE cur
DEALLOCATE cur
EXEC (@sele)
END
IF UPPER(@id) = 'C'
BEGIN
-- C按雇用性质
DECLARE @employ VARCHAR(2)
DECLARE @employname VARCHAR(16)
SET @sele = 'select left(space(2*(departmentcode.rating))+departmentcode.departmentname,50) as 部门 ,'
DECLARE cur CURSOR FOR
SELECT employ,employname FROM employcode
OPEN cur
FETCH NEXT FROM cur INTO @employ,@employname
WHILE (@@fetch_status = 0)
BEGIN
SET @sele = @sele +' (select count(*) from person where person.department like departmentcode.department+'+'''%'' AND person.employ ='''+@employ+''') as '''+ @employname +''','
FETCH NEXT FROM cur INTO @employ,@employname
END
SET @sele = left(@sele,len(@sele)-1)
SET @sele = @sele +' from departmentcode '
SET @sele = @sele +' where departmentcode.rating <= '+ cast(@rating as varchar(2))
SET @sele = @sele +' order by departmentcode.myorder '
CLOSE cur
DEALLOCATE cur
EXEC (@sele)
END
IF UPPER(@id) = 'D'
BEGIN
-- A按学历
DECLARE @edu VARCHAR(2)
DECLARE @eduname VARCHAR(16)
SET @sele = 'select left(space(2*(departmentcode.rating))+departmentcode.departmentname,50) as 部门 ,'
DECLARE cur CURSOR FOR
SELECT edu,eduname FROM educode
OPEN cur
FETCH NEXT FROM cur INTO @edu,@eduname
WHILE (@@fetch_status = 0)
BEGIN
SET @sele = @sele +' (select count(*) from person where person.department like departmentcode.department+'+'''%'' AND person.edu ='''+@edu+''') as '''+ @eduname +''','
FETCH NEXT FROM cur INTO @edu,@eduname
END
SET @sele = left(@sele,len(@sele)-1)
SET @sele = @sele +' from departmentcode '
SET @sele = @sele +' where departmentcode.rating <= '+ cast(@rating as varchar(2))
SET @sele = @sele +' order by departmentcode.myorder '
CLOSE cur
DEALLOCATE cur
EXEC (@sele)
END
IF UPPER(@id) = 'E'
BEGIN
-- D按就职状态
DECLARE @jobstate VARCHAR(2)
DECLARE @jobstatename VARCHAR(16)
SET @sele = 'select left(space(2*(departmentcode.rating))+departmentcode.departmentname,50) as 部门 ,'
DECLARE cur CURSOR FOR
SELECT jobstate,jobstatename FROM jobstatecode order by jobstate
OPEN cur
FETCH NEXT FROM cur INTO @jobstate,@jobstatename
WHILE (@@fetch_status = 0)
BEGIN
SET @sele = @sele +' (select count(*) from person where person.department like departmentcode.department+'+'''%'' AND person.jobstate ='''+@jobstate+''') as '''+ @jobstatename +''','
FETCH NEXT FROM cur INTO @jobstate,@jobstatename
END
SET @sele = left(@sele,len(@sele)-1)
SET @sele = @sele +' from departmentcode '
SET @sele = @sele +' where departmentcode.rating <= '+ cast(@rating as varchar(2))
SET @sele = @sele +' order by departmentcode.myorder '
CLOSE cur
DEALLOCATE cur
EXEC (@sele)
END
IF UPPER(@id) = 'F'
BEGIN
-- E按性别
SET @sele = 'select left(space(2*(departmentcode.rating))+departmentcode.departmentname,50) as 部门 ,'
SET @sele = @sele +'(select count(*) from person where person.department like departmentcode.department+'+'''%'' AND person.sex =''男'') as '' 男 '','
SET @sele = @sele +'(select count(*) from person where person.department = departmentcode.department+'+'''%'' AND person.sex <>''男'') as '' 女 '','
SET @sele = @sele +'(select count(*) from person where person.department like departmentcode.department+''%'') as '' 合计 '','
SET @sele = left(@sele,len(@sele)-1)
SET @sele = @sele +' from departmentcode '
SET @sele = @sele +' where departmentcode.rating <= '+ cast(@rating as varchar(2))
SET @sele = @sele +' order by departmentcode.myorder '
EXEC (@sele)
RETURN
END
IF UPPER(@id) = 'G'
BEGIN
-- F按婚姻状况
SET @sele = 'select left(space(2*(departmentcode.rating))+departmentcode.departmentname,50) as 部门 ,'
SET @sele = @sele +'(select count(*) from person where person.department like departmentcode.department+'+'''%'' AND person.marital =''已婚'') as '' 已婚 '','
SET @sele = @sele +'(select count(*) from person where person.department like departmentcode.department+'+'''%'' AND person.marital <>''已婚'') as '' 未婚 '','
SET @sele = @sele +'(select count(*) from person where person.department like departmentcode.department+''%'') as '' 合计 '','
SET @sele = left(@sele,len(@sele)-1)
SET @sele = @sele +' from departmentcode '
SET @sele = @sele +' where departmentcode.rating <= '+ cast(@rating as varchar(2))
SET @sele = @sele +' order by departmentcode.myorder '
EXEC (@sele)
END
IF UPPER(@id) = 'H'
BEGIN
-- G按民族
DECLARE @race VARCHAR(30)
SET @sele = 'select left(space(2*(departmentcode.rating))+departmentcode.departmentname,50) as 部门 ,'
DECLARE cur CURSOR FOR
SELECT race FROM racecode ORDER BY id
OPEN cur
FETCH NEXT FROM cur INTO @race
WHILE (@@fetch_status = 0)
BEGIN
SET @sele = @sele +' (select count(*) from person where person.department like departmentcode.department+'+'''%'' AND person.race ='''+@race+''') as '''+ @race +''','
FETCH NEXT FROM cur INTO @race
END
SET @sele = left(@sele,len(@sele)-1)
SET @sele = @sele +' from departmentcode '
SET @sele = @sele +' where departmentcode.rating <= '+ cast(@rating as varchar(2))
SET @sele = @sele +' order by departmentcode.myorder '
CLOSE cur
DEALLOCATE cur
EXEC (@sele)
END
IF UPPER(@id) = 'I'
BEGIN
-- h按政治面貌
DECLARE @political VARCHAR(30)
SET @sele = 'select left(space(2*(departmentcode.rating))+departmentcode.departmentname,50) as 部门 ,'
DECLARE cur CURSOR FOR
SELECT political FROM politicalcode ORDER BY id
OPEN cur
FETCH NEXT FROM cur INTO @political
WHILE (@@fetch_status = 0)
BEGIN
SET @sele = @sele +' (select count(*) from person where person.department like departmentcode.department+'+'''%'' AND person.political ='''+@political+''') as '' '+ @political +' '','
FETCH NEXT FROM cur INTO @political
END
SET @sele = left(@sele,len(@sele)-1)
SET @sele = @sele +' from departmentcode '
SET @sele = @sele +' where departmentcode.rating <= '+ cast(@rating as varchar(2))
SET @sele = @sele +' order by departmentcode.myorder '
CLOSE cur
DEALLOCATE cur
EXEC (@sele)
END
IF UPPER(@id) = 'J'
BEGIN
-- I按职务
DECLARE @assignment VARCHAR(30)
SET @sele = 'select left(space(2*(departmentcode.rating))+departmentcode.departmentname,50) as 部门 ,'
DECLARE cur CURSOR FOR
SELECT DISTINCT assignment FROM person ORDER BY assignment DESC
OPEN cur
FETCH NEXT FROM cur INTO @assignment
WHILE (@@fetch_status = 0)
BEGIN
IF (@assignment = '') OR (@assignment = null)
BEGIN
SET @assignment = '无'
END
SET @sele = @sele +' (select count(*) from person where person.department like departmentcode.department+'+'''%'' AND person.assignment ='''+@assignment+''') as '' '+ @assignment +' '','
FETCH NEXT FROM cur INTO @assignment
END
SET @sele = left(@sele,len(@sele)-1)
SET @sele = @sele +' from departmentcode '
SET @sele = @sele +
SQL server 2005 存储过程100个实例
4星 · 超过85%的资源 需积分: 9 77 浏览量
2009-07-08
00:17:39
上传
评论 3
收藏 33KB RAR 举报 
sql server 存储过程100例.rar （25个子文件） 
sql server 存储过程100例 
库存存储过程.txt 4KB 存储过程,插数据进一个表.txt 316B 20.txt 565B
12.sql 4KB 15.sql 3KB 5.txt 1KB 18.sql 2KB 20.sql 5KB 11.sql 3KB 1114.sql 84KB 17.sql 6KB 4.txt 925B 6.txt 1KB 3.txt 470B 7.txt 914B 14.sql 4KB 19.sql 2KB 8.txt 787B 16.sql 4KB 9.txt 978B 1112.sql 156KB 10.txt 969B 1113.sql 1KB 2.txt 11KB 存储过程1.txt 4KB资源评论
yazhen06072011-12-26不会用啊，里面怎么没说怎么个用法啊
ivwseeqg2011-11-09讲的很详细，非常好
hitwengqi
- 粉丝: 789
- 资源: 24
最新资源
- 常用工具集参考用于图像等数据处理
- 音乐展示网页、基于Stenography的图像数字水印添加与提取，以及基于颜色矩和Tamura算法的图像相似度评估算法py源码
- 基于EmguCV(OpenCV .net封装)，图像数字水印加解密算法的实现,其中包含最低有效位算法,离散傅里叶变换算法+文档书
- 基于matlab+DWT的图像水印项目，数字水印+源代码+文档说明+图片+报告pdf
- (优秀毕业设计)基于python实现的数字图像可视化水印系统的设计与实现，多种数字算法实现+源代码+文档说明+理论演示pdf
- 基于DWT-DCT-SVD和deflate压缩的数字水印方法python源码+Gui界面+演示视频(高分毕业设计)
- 基于matlab实现DWT、DCT、SVD算法数字图像水印可视化系统+GUI界面+文档说明+详细注释(高分毕业设计)
- NCIAE-Data-Structure大一大二笔记
- 学习wireshark笔记
- digital-image-数据可视化笔记
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
