Hello,

I am getting the following error when I run this stored procedure:

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'PersonID'.

Any idea why I am getting this error?

Thanks,

TFISHER

USE [AMNetDesignMM]
GO
/****** Object:  StoredProcedure [dbo].[PersonSearch]    Script Date: 03/16/2010 13:05:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER   PROC [dbo].[PersonSearch]
(@FirstName	varchar(30) = NULL
,@LastName  varchar(30) = NULL
,@MiddleName  varchar(20) = NULL
,@DateOfBirth varchar(8)  = NULL
,@SocialSecurityNumber   varchar(12) = NULL
,@Gender    char(1)     = NULL
,@Race   char(1)     = NULL
,@debug   smallint    = 0
)

AS

DECLARE @sqlstring	varchar(5000)
,@akastring varchar(5000)

SET NOCOUNT ON

CREATE TABLE #idtable
(cid       uniqueidentifier)

SET @sqlstring = ''
SET @akastring = ''

-- BUILD WHERE CLAUSE
if @FirstName IS NOT NULL
begin
	SET @sqlstring='FirstName LIKE '''+rtrim(@FirstName)+'%'' AND '
	SET @akastring='a.FirstName LIKE '''+rtrim(@FirstName)+'%'' AND '
end

if @LastName IS NOT NULL
begin
	SET @sqlstring=@sqlstring + 'LastName LIKE '''+rtrim(@LastName)+'%'' AND '
	SET @akastring=@akastring + 'a.LastName LIKE '''+rtrim(@LastName)+'%'' AND '
end

if @MiddleName IS NOT NULL
begin
	SET @sqlstring=@sqlstring + 'MiddleName LIKE '''+rtrim(@MiddleName)+'%'' AND '
	SET @akastring=@akastring + 'a.MiddleName LIKE '''+rtrim(@MiddleName)+'%'' AND '
end

if @DateOfBirth IS NOT NULL
begin
	SET @sqlstring=@sqlstring + 'DateOfBirth = '''+rtrim(@DateOfBirth)+''' AND '
	SET @akastring=@akastring + 'a.DateOfBirth = '''+rtrim(@DateOfBirth)+''' AND '
end

if @SocialSecurityNumber IS NOT NULL
begin
	SET @sqlstring=@sqlstring + 'c LIKE '''+rtrim(@SocialSecurityNumber)+'%'' AND '
	SET @akastring=@akastring + 'a.SocialSecurityNumber LIKE '''+rtrim(@SocialSecurityNumber)+'%'' AND '
end

if @Gender IS NOT NULL
begin
	SET @sqlstring=@sqlstring + 'Gender = '''+@Gender+''' AND '
	SET @akastring=@akastring + 'p.Gender = '''+@Gender+''' AND '
end

if @Race IS NOT NULL
begin
	SET @sqlstring=@sqlstring + 'Race = '''+@Race+''' AND '
	SET @akastring=@akastring + 'p.Race = '''+@Race+''' AND '
end

IF @sqlstring = '' and @akastring = ''
BEGIN
	RAISERROR ('A selection criteria is required',11,1)
	RETURN
END

-- REMOVE TRAINING AND CLAUSE
IF len(@sqlstring) > 0
	SET @sqlstring = LEFT(@sqlstring, len(rtrim(@sqlstring))-3)
IF len(@akastring) > 0
	SET @akastring = LEFT(@akastring, len(rtrim(@akastring))-3) 

-- IF @debug = 1
-- 	PRINT 'Person WHERE '+@sqlstring
-- IF @debug = 1
-- 	PRINT '  Akas WHERE '+@akastring

IF len(@sqlstring) > 0
	EXEC ('INSERT INTO #idtable SELECT PersonID FROM dbo.person WHERE '+@sqlstring)

IF len(@akastring) > 0
	EXEC ('INSERT INTO #idtable SELECT PersonID AS Tim FROM dbo.aka a JOIN dbo.person p ON a.PersonID = p.PersonID WHERE '+@akastring)

SELECT person.PersonID, person.LastName, person.FirstName, person.Gender
, person.Race, person.Height, person.Weight, person.EyeColor, person.HairColor
, person.DateOfBirth, person.SocialSecurityNumber
, IsNull(LTRIM(RTRIM(Aka.FirstName)+' '+ RTRIM(Aka.LastName)),'') as aka_cname 
FROM #idtable 
JOIN dbo.person 	 ON #idtable.cid=person.PersonID 
LEFT JOIN dbo.[Aka] ON person.PersonID=aka.PersonID 
ORDER BY person.LastName, person.FirstName

DROP TABLE #idtable

RETURN
< /pre>

Thanks,

TFISHER