Greg, This error usually indicates a failure to properly alias during a join - where the driver can't figure out which table to pull "jobdescId" from.
Mark -----Original Message----- From: Greg Jordan [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 18, 2002 9:42 AM To: CF-Talk 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] ______________________________________________________________________ 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
