Invalid Column Name error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Invalid Column Name error

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-14 : 02:26:46

I've been recieving the following error:

-----------------------
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Glenorchy'.
-----------------------
when executing the proc below:

CREATE PROCEDURE uspSearch
(
@suburbtown varchar(30) = NULL
)

AS

DECLARE @strSQL Varchar(500)
DECLARE @strSuburbTown VarChar(30)

SET @strSQL = 'Select B.business_id, B.rank, B.business_name, B.suburb_town,'
SET @strSQL = @strSQL + ' B.postcode, B.phone, B.fax, B.status, BF.description'
SET @strSQL = @strSQL + ' FROM BUSINESS B left join BUSINESSFULL BF ON B.business_id '
SET @strSQL = @strSQL + ' = BF.business_id WHERE B.suburb_town = ' + @SuburbTown

EXEC(@strSQL)

The "suburb_town" column in the database is Varchar(30) as well, so I am not sure why I am getting the error when I try to execute the SPROC with the following code
----------------------------
exec uspSearch 'Glenorchy'

Glenorchy is the first value in the "suburb_town" column of the database.

Any ideas?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-14 : 02:54:53

Try printing the string then you will see that it should be

SET @strSQL = @strSQL + ' = BF.business_id WHERE B.suburb_town = ''' + @SuburbTown + ''''

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-14 : 03:39:29

Thanks NR. That's a handy hint ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources