案例一、
ALTER PROCEDURE [dbo].[pro_Purchase_hdy]
@departmentname nvarchar(200),
@StartDate datetime,
@EndDate datetime
AS
BEGIN
set nocount on;
DECLARE @Parm NVARCHAR(MAX) = N'',
@sqlcommand NVARCHAR(MAX) = N''
SET @sqlcommand = 'SELECT a.sqrq,hr.lastname,hd.departmentname,a.wsmc,a.lcbh,b.kpfqc,b.fph,b.bhsje,b.sl,b.se,b.jshj FROM formtable_main_495 a, formtable_main_495_dt1 b,workflow_requestbase c, HrmResource hr,HrmDepartment hd WHERE a.id=b.mainid AND a.requestId=c.requestId and a.sqr=hr.id and a.sqbm=hd.id and c.status=''归档'''
IF(@departmentname IS NOT NULL and @departmentname!='')
SET @sqlcommand += ' AND departmentname like ''%'+@departmentname+'%'' order by a.sqrq desc'
IF(@StartDate IS NOT NULL and @StartDate!='')
SET @sqlcommand += ' AND a.sqrq>=@StartDate'
IF(@EndDate IS NOT NULL and @EndDate!='')
SET @sqlcommand += ' AND a.sqrq<=@EndDate'
IF(@departmentname ='' and @StartDate='' and @EndDate='')
SET @sqlcommand += ' order by a.sqrq desc'
SET @Parm= '@departmentname varchar(50),
@StartDate datetime,
@EndDate datetime'
PRINT @sqlcommand
EXEC sp_executesql @sqlcommand,@Parm,
@departmentname = @departmentname,
@StartDate = @StartDate,
@EndDate = @EndDate
END
案例二、
ALTER PROCEDURE [dbo].[pro_htdq_hdy]
@lastname nvarchar(200),
@departmentname nvarchar(200)
AS
BEGIN
if ( @lastname='' and @departmentname='')
begin
SELECT field7 lastname,departmentmark departmentname,enddate as enddate,field53 field53,field52 field52,field42,field43,field55,field54,field44,field45,field57,field56,field46,field47,field59,field58,field48,field49,field61,field60,field50,field51,(select DATEDIFF(DAY,(SELECT DATENAME(year,GETDATE())+'-'+ DATENAME(MONTH,GETDATE())+'-'+ DATENAME(DAY,GETDATE())),enddate)) as data FROM cus_fielddata cf,HrmResource hr,HrmDepartment h where cf.id=hr.id and h.id=hr.departmentid and cf.field7<>'' and field29=0 ORDER BY data
end
else
begin
SELECT field7 lastname,departmentmark departmentname,enddate as enddate,field53 field53,field52 field52,field42,field43,field55,field54,field44,field45,field57,field56,field46,field47,field59,field58,field48,field49,field61,field60,field50,field51,(select DATEDIFF(DAY,(SELECT DATENAME(year,GETDATE())+'-'+ DATENAME(MONTH,GETDATE())+'-'+ DATENAME(DAY,GETDATE())),enddate)) as data FROM cus_fielddata cf,HrmResource hr,HrmDepartment h where
cf.id=hr.id and h.id=hr.departmentid and cf.field7<>'' and field29=0
and lastname= case when @lastname<>'' then @lastname else field7 end
and departmentname= case when @departmentname<>'' then @departmentname else departmentname end
ORDER BY data
end
END
什么是存储过程?
存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。
由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中,所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息,所以在一定程度上也可以减少网络流量、简单网络负担。
存储过程创建语法
create proc | procedure pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements
操作实例
首先创建两张表
Student表
Course表
其中Course表中的F_StuId为Student表的F_ID是外键关联
增加测试数据,顺便练一下sql语法
DECLARE @i INT, @F_Name VARCHAR(50), @F_Age INT, @F_Sex VARCHAR(2), @F_Hobby VARCHAR(50), @ranNum INT
SET @i = 0
SET @F_Name = '小明'
SET @F_Age = 0
WHILE @i < 100
BEGIN
SET @ranNum = RAND() * 4 --生成一个0-3的随机数
IF @ranNum = 0
BEGIN
SET @F_Sex = '男'
SET @F_Hobby = '篮球'
END
ELSE IF @ranNum = 1
BEGIN
SET @F_Sex = '男'
SET @F_Hobby = '足球'
END
ELSE IF @ranNum = 2
BEGIN
SET @F_Sex = '女'
SET @F_Hobby = '刺绣'
END
ELSE
BEGIN
SET @F_Sex = '女'
SET @F_Hobby = '跳绳'
END
INSERT INTO Student (F_Name, F_Age, F_Sex, F_Hobby) VALUES (@F_Name + CONVERT(VARCHAR(3), @i), @F_Age + @i, @F_Sex, @F_Hobby)
SET @i = @i + 1
END
GO
DECLARE @i INT, @F_StuId INT, @F_Course VARCHAR(50), @F_Score INT, @ranNum INT
SET @i = 0
SET @F_StuId = 0
SET @F_Course = '语文'
SET @F_Score = 0
WHILE @i < 100
BEGIN
SET @ranNum = RAND() * 3
IF @ranNum = 0
BEGIN
SET @F_Course = '语文'
END
ELSE IF @ranNum = 1
BEGIN
SET @F_Course = '数学'
END
ELSE
BEGIN
SET @F_Course = '英语'
END
INSERT INTO Course (F_StuId, F_Course, F_Score) VALUES (@F_StuId + @i, @F_Course, @F_Score + @i)
SET @i = @i + 1
END
之后我们根据存储过程的语法创建一个无参的存储过程
create proc proc_get_student
as
select * from Student
创建好以后我们可以在可编程性中找到我们新建的存储过程
然后执行这个存储过程
exec proc_get_student
执行结果
接下来我们来创建一个带参数的存储过程
create proc proc_find_stu(@startId int, @endId int)
as
select * from Student where F_ID between @startId and @endId
执行存储过程(取到id为10-20的学生信息)
exec proc_find_stu 10,20
执行结果
接下来我通过一个存储过程来完成联表查询+分页显示
create proc proc_page(@currPage int, @pageSize int, @keyWord varchar(50))
as
select a.F_ID, a.F_Name, a.F_Sex, b.F_Course, b.F_Score from Student a left join Course b on a.F_ID = b.F_StuId
where [F_Name] like '%' + @keyWord + '%'
order by a.F_ID
offset ((@currPage - 1) * @pageSize) rows
fetch next @pageSize rows only
执行该存储过程
exec proc_page 1, 10, '0'
执行结果
如有需要,请联系微信:hdyi1997 同时请说明来意,共同进步!!!