This is very bad code. Don't use string concatenation, use sp_ExecuteSQL instead and pass your parameters.

Check this link

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

As for your problem - when you select from multiple tables having the same field, you need to add an alias to the field indicating which table it comes from.

In fact, always add aliases even if the fields are unique when selecting from multiple tables (for documentation purpose, at least).

The code, as is, would not work if you pass a field with single quote in it - you'll have to double it.


>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
>