IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs J JOIN msdb.dbo.sysjobactivity A ON A.job_id=J.job_id WHERE J.name=N'delete resume_x_job_position' AND A.run_requested_date IS NOT NULL AND A.stop_execution_date IS NULL)
    select 'The job is running!'
ELSE
    select 'The job is not running.'

USE [swap_zhaopin]
GO
/****** Object: StoredProcedure [zhaopin].[PKG_Get_Position_ExportInterfaceBySougouSearch] Script Date: 11/26/2015 6:51:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



-- *************************************************************
-- CHANGE LOG
-- *************************************************************
-- Author : jackson.zhang
-- Auditor :
-- Modified : 2013-07-10
-- Task No :
-- Requirement :
-- Purpose :
-- Scope :
-- Risk: [无风险]
-- Approved by: [审批者]
-- *************************************************************
e
create PROC [zhaopin].[PKG_Get_Position_ExportInterfaceBySougouSearch] --create为新建，alter为修改
@ErrorCode BIGINT OUTPUT --错误Flag
AS
SET NOCOUNT ON
SET @ErrorCode = 0
DECLARE @today DATETIME
DECLARE @StartDate DATETIME
SET @today = GETDATE()
SET @StartDate = DATEADD(d, -1, @today)
SELECT
c1.id AS RootCompanyID ,--总公司编号
c.id AS CompanyID ,--分公司编号
c1.company_name AS RootCompanyName ,--总公司名称
c.company_name AS CompanyName ,--分公司名称
c.intro_url, --公司url
c1.company_size ,
c1.company_type ,
c.industry_id ,
j.job_title ,
j.city_id ,
j.url ,
j.job_type , --职位大类
jpst.subjobtype,--职位小类
j.min_education_level , --学历
CASE WHEN j.min_years <> -1
THEN RIGHT(( '000' + CAST(ISNULL(j.min_years, 0) AS VARCHAR) ), 4)
ELSE CAST(j.min_years AS VARCHAR)
END AS min_years , --工作经历
j.date_refresh AS date_start ,
j.date_end ,
j.date_refresh ,
CASE WHEN ( ISNULL(j.min_salary, 0) + ISNULL(j.max_salary, 0) ) <= 0
THEN '0000000000'
ELSE RIGHT(( '00000' + CAST(ISNULL(j.min_salary, 0) AS VARCHAR) ), 5) + RIGHT(( '00000' + CAST(ISNULL(j.max_salary, 0) AS VARCHAR) ), 5)
END AS salary ,
j.job_position_number ,
j.version_number ,
j.status ,
j.employment_type,
j.management_experience_required AS IsShowSalary,
c.c_name,
j.quantity,
ld.cn_name,
c2.company_name AS DepartName --部门名称
FROM zhaopin.job_position_partab j WITH ( NOLOCK )
INNER JOIN zhaopin.company c WITH ( NOLOCK ) ON j.company_id = c.id
INNER JOIN zhaopin.company c1 WITH ( NOLOCK ) ON c.root_company_id = c1.id
INNER JOIN zhaopin.company c2 WITH ( NOLOCK ) ON j.department_id = c2.id
LEFT JOIN [zhaopin].[Location_District] ld with(nolock) ON j.cq_id=ld.cq_id
INNER JOIN zhaopin.job_position_x_subtype jpst with(nolock)
on jpst.job_position_number = j.job_position_number
WHERE j.is_active = 'y'
AND j.[status] =3
AND j.is_editable = 'y'
AND j.date_start BETWEEN @StartDate AND @today
AND j.date_end> DATEADD(d, 1, @today)

ITPub博客

sqlserver查看存储过程的执行

最新文章