Greg, categoryJobDescrID is in both you categoryJobDescr and categoryJobListing table.
The procedure looks ok, so I can only assume that you are passing through categoryJobDescrID as one of the fields in @orderby without a tablename prefix. Regards Stephen ----- Original Message ----- From: "Greg Jordan" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, April 18, 2002 3:41 PM Subject: stored procedure - Ambiguous column name > 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] > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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