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
