create proc P_am_postjob_search
(
@searchtime varchar(5),
@search_poststatus char(1),
@search_applystatus char(1),
@job_name varchar(50),
@Re_deptid varchar(20)
)
as


declare @m_strSqlsearchtime varchar(255)
declare @m_strSqlpoststatus varchar(255) --Sql语句的条件部分
declare @m_strSqlapplystatus varchar(255) --Sql语句的条件部分
declare @m_strSqljob_name varchar(255) --Sql语句的条件部分
declare @m_strSqlRe_deptid varchar(255) --Sql语句的条件部分


select @m_strSqlsearchtime= case
when @searchtime=\'0\' then /*申请时间*/
\'select Re_NO,post_id,job_name,Re_num,Re_deptid,Re_applydate,job_poststatus,job_posttype,job_posttype,job_applystatus from am_postjob where 1=1\'
else
\'select Re_NO,post_id,job_name,Re_num,Re_deptid,Re_applydate,job_poststatus,job_posttype,job_posttype,job_applystatus from am_postjob where \'+@searchtime+\'>datediff(dd,Re_applydate,getdate())\'
end

select @m_strSqlpoststatus= case
when @search_poststatus<>\'n\' /*发布状态*/
then \' and job_poststatus=\'\'\'+@search_poststatus+\'\'\'\'
else \'\'
end
select @m_strSqlapplystatus= case
when @search_applystatus<>\'n\' /*招聘状态*/
then \' and job_applystatus=\'\'\'+@search_poststatus+\'\'\'\'
else
\'\'
end
select @m_strSqljob_name= case
when @job_name<>\'\' /*职位关键字*/
then \' and job_name like \'\'%\'+@job_name+\'%\'\'\'
else
\'\'
end
select @m_strSqlRe_deptid
= case
when @Re_deptid<>\'\' /*申请部门ID*/
then \' and Re_deptid=\'\'\'+@Re_deptid+\'\'\'\'
else
\'\'
end
exec ( @m_strSqlsearchtime + @m_strSqlpoststatus + @m_strSqlapplystatus + @m_strSqljob_name + @m_strSqlRe_deptid +\'order by post_id asc\')


ASP中调用:
set objcmd=server.CreateObject ("adodb.command")
objcmd.ActiveConnection=cn
objcmd.CommandText="p_am_postjob_search"
objcmd.CommandType=4
objcmd.Parameters.append objcmd.CreateParameter("@searchtime",202,1,5,search_time)
objcmd.Parameters.append objcmd.CreateParameter("@search_poststatus",202,1,1,search_poststatus)
objcmd.Parameters.append objcmd.CreateParameter("@search_applystatus",202,1,1,search_applystatus)
objcmd.Parameters.append objcmd.CreateParameter("@job_name",202,1,50,job_name)
objcmd.Parameters.append objcmd.CreateParameter("@Re_deptid",202,1,20,Re_deptid)
set rs=objcmd.Execute