Dynamic SQL Invalid Column name error

Any idea why this is telling me that the field value I want to pass in
is an invalid column name?
This is for a "Search By" query. I have a drop down of choices and a
text box for the value. I need to search for the value entered in the
text box in the drop down field. The list is populated from fields in
more than 1 table.
STORED PROC:
ALTER PROCEDURE dbo.searchQuery2
(
@.searchTxt varchar(50) = NULL, @.searchField varchar(50)
)
AS
Declare @.sql varchar(4000)
Select @.sql = 'Select r.requestID, r.projectManager, r.projectName,
r.dateSubmitted, a.Name
From Request r, AppComm a
Where ' + @.searchField + ' = ' + @.searchTxt + 'and r.reviewedBy =
a.badgeID'
Select @.sql
exec (@.sql)
OUTPUT:
Select r.requestID, r.projectManager, r.projectName, r.dateSubmitted,
a.Name
From Request r, AppComm a
Where sumbittedBy = a111111 and r.reviewedBy = a.badgeID
Invalid column name 'a111111'.
(1 row(s) returned)
@.RETURN_VALUE = 0
Finished running dbo."searchQuery2".
Why is it returning the correct SQL but telling me that a111111 is a
column? - it is not a column at all, it is the value. I have tried
playing around with single and double quotes but nothing seems to work.
Please help.
Thanks!Because the value a11111 is a string, and you need to enclose string in sing
le quotes:

> Where ' + @.searchField + ''' = ' + @.searchTxt + ''' and r.reviewedBy =
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<Shenoy.D@.gmail.com> wrote in message news:1147981295.111755.280880@.j73g2000cwa.googlegroup
s.com...
> Any idea why this is telling me that the field value I want to pass in
> is an invalid column name?
> This is for a "Search By" query. I have a drop down of choices and a
> text box for the value. I need to search for the value entered in the
> text box in the drop down field. The list is populated from fields in
> more than 1 table.
> STORED PROC:
> ALTER PROCEDURE dbo.searchQuery2
> (
> @.searchTxt varchar(50) = NULL, @.searchField varchar(50)
> )
> AS
> Declare @.sql varchar(4000)
> Select @.sql = 'Select r.requestID, r.projectManager, r.projectName,
> r.dateSubmitted, a.Name
> From Request r, AppComm a
> Where ' + @.searchField + ' = ' + @.searchTxt + 'and r.reviewedBy =
> a.badgeID'
> Select @.sql
> exec (@.sql)
>
> OUTPUT:
> Select r.requestID, r.projectManager, r.projectName, r.dateSubmitted,
> a.Name
> From Request r, AppComm a
> Where sumbittedBy = a111111 and r.reviewedBy = a.badgeID
>
> Invalid column name 'a111111'.
> (1 row(s) returned)
> @.RETURN_VALUE = 0
> Finished running dbo."searchQuery2".
> Why is it returning the correct SQL but telling me that a111111 is a
> column? - it is not a column at all, it is the value. I have tried
> playing around with single and double quotes but nothing seems to work.
> Please help.
> Thanks!
>