commJobAdd代码如下：
Alter PROCEDURE spAddJob
-- Declare variables for inserts to 2 tables
(@CompanyID [int], @JobTypeID [int], @RegionID [int], @JobTitle [varchar](100), @StartDate [datetime], @CloseDate [datetime], @JobDescription [varchar](4000), @Qualifications [varchar](4000), @SalaryBenefits [varchar](4000), @Resp [varchar](4000), @Availability [varchar](200), @ToApply [varchar](2000), @EmailJobs [varchar](100), @CategoryID [int], @JobID [int] OUTPUT)
AS
-- Insert into Jobs table
INSERT INTO Jobs ([CompanyID], [JobTypeID], [RegionID], [JobTitle], [StartDate], [CloseDate], [JobDescription], [Qualifications], [SalaryBenefits], [Resp], [Availability], [ToApply], [EmailJobs])
VALUES (@CompanyID, @JobTypeID, @RegionID, @JobTitle, @StartDate, @CloseDate, @JobDescription, @Qualifications, @SalaryBenefits, @Resp, @Availability, @ToApply, @EmailJobs)
-- Retrieve the automatically generated JobID VALUE from the Jobs table
SET @JobId = @@IDENTITY
-- Insert new values into JobCategory table
INSERT INTO JobCategory (JobID, CategoryID)
VALUES (@JobID, @CategoryID)
Return
spJobSearch代码如下：
Alter Procedure spJobID
@JobID int
As
SELECT Jobs.*, Company.*, Region.Region, JobType.JobType
FROM Jobs INNER JOIN
Region ON Jobs.RegionID = Region.RegionID INNER JOIN
JobType ON Jobs.JobTypeID = JobType.JobTypeID INNER JOIN
Company ON Jobs.CompanyID = Company.CompanyID
WHERE JobID = @JobID AND Jobs.Verify = 1
SpJobCategories代码如下：
Alter Procedure spJobCategories
@JobID int
As
SELECT JobCategory.JobID, JobCategory.CategoryID, CategoryType.Category
FROM CategoryType INNER JOIN JobCategory
ON JobCategory.CategoryID = CategoryType.CategoryID
WHERE JobCategory.JobID = @JobID ORDER BY CategoryType.Category
return
|
