February 24, 2006 at 5:02 am
I am working on a database with several small tables containing look-up values, all in the same format ID (int) and Name (varchar 30).
I am trying to create a generic stored procedure that will take a serch string and query it against the Name column of the table specified as the other parameter. The stored procedure is fine, it is when I come to testing it that I get the message "Invalid column Name 'Hospital'. It works as long as I keep the WHERE clause out of the equation, as soon as that is added I get an error message.
It is probably something very obvious, but since I am just starting out with SQL Server, it is bafling me... It is running on a SQL Server 2005. Help very much appreciated
CREATE PROCEDURE stp_SearchTypeTable
@tablename varchar(100),
@SearchString varchar(30)
AS
DECLARE @qry varchar(500)
SET @qry =
'SELECT * FROM ' + @tablename + ' WHERE ' + @tablename + '.Name LIKE ' + @SearchString + ' ORDER BY ' + @tablename + '.Name'
EXEC (@qry)
GO
exec stp_SearchTypeTable 'dbo.AddressType', 'Hospital'
GO
February 24, 2006 at 7:04 am
I don't have an answer why, but you may want to try to alias the table in the query:
SET @qry = 'SELECT * FROM ' + @tablename + ' t WHERE t.Name LIKE ' + @SearchString + ' ORDER BY t.Name'
Mark
February 24, 2006 at 7:40 am
Hi Kris,
Try...
SET @qry =
'SELECT * FROM ' + @tablename + ' WHERE ' + @tablename + '.Name LIKE ''%' + @SearchString + '%'' ORDER BY ' + @tablename + '.Name'
Darko
February 24, 2006 at 7:43 am
Thanks Darko,
That is actually working, both with and without the %
Brilliant, save my weekend that!
Kris
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply