i've been trying to work this out for about a week and i am stumped. i keep
getting an error related to an ambiguous column name. I've checked the sp
over and over (with my admittedly sub-par sql skills) and cannot find
anything wrong. i've included the error and the sp below. any help is
appreciated very much. thanks....
*******************error message***********************
Error occured at: 04/17/02 16:05:24
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR
1.0.3705)
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name
'categoryJobDescrID'.
SQL = "sp_jobs_select"
Data Source = " ********** "
*******************error message***********************
*******************stored procedure***********************
CREATE PROCEDURE [yourconsultant].[sp_jobs_select] @keyword varchar(100),
@locationID varchar(500), @categoryJobDescrID varchar(500), @page int, @size
int, @orderby varchar(100), @count int output
AS
DECLARE @sql nvarchar(2000), @where nvarchar(1000)
SET @WHERE='0=0'
IF @keyword IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND jobs.jobName LIKE ' +'''' + @keyword + '%'' OR
jobs.jobDescription LIKE ' +'''' + @keyword + '%'''
END
IF @locationID IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND locationJob.locationID IN (' + @locationID + ')'
END
IF @categoryJobDescrID IS NOT NULL
BEGIN
SET @WHERE = @WHERE + ' AND categoryJobListing.categoryJobDescrID IN (' +
@categoryJobDescrID + ') '
END
SET @sql =(' SELECT jobs.jobid, jobs. empid, jobs.jobname,
jobs.datetimeentered, location.location,
categoryJobDescr.categoryjobdescrname, employers.empName
FROM jobs
JOIN locationJob ON jobs.jobID = locationJob.jobID
JOIN categoryJobListing ON jobs.jobID = categoryJobListing.jobID
JOIN location ON location.locationID = locationJob.locationID
JOIN categoryJobDescr ON categoryJobDescr.categoryJobDescrID
=categoryJobListing.categoryJobDescrID
JOIN employers ON employers.empID = jobs.empID
WHERE ' +@WHERE+'
ORDER BY '+@orderby)
DECLARE @Start int, @End int
SET @Start = (((@Page - 1) * @Size) + 1)
SET @End = (@Start + @Size - 1)
CREATE TABLE #temp
(Row int IDENTITY(1,1) PRIMARY KEY,
JOBID int, EMPID int,
JOBNAME varchar(100),
DATETIMEENTERED datetime,
LOCATION varchar(150),
CATEGORYJOBDESCRNAME varchar(75),
EMPNAME varchar(100)
)
INSERT INTO #temp
EXEC(@sql)
BEGIN
SELECT categoryjobdescrname, datetimeentered, empid, empname, jobid,jobname,
location
FROM #temp
WHERE (Row >= @Start) AND (Row <= @End)
SET @count = (select count(*) from #temp)
END
DROP TABLE #temp
GO
*******************stored procedure***********************
-------------------------
Greg Jordan
Certified ColdFusion Developer
Web Project Manager
[EMAIL PROTECTED]
---
[This E-mail scanned for viruses by Declude Virus]
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
