Link to home
Start Free TrialLog in
Avatar of gwarcher
gwarcher

asked on

Invalid Column error in where clause on stored procedure execution

I have a basic stored procedure that I created that is failing with an invalid collumn name error when executed. The only problem is that the variable being passed goes into the where clause and is not a column name.



ALTER PROCEDURE [dbo].[spSearchFirstName]
      @FirstName varchar(155)
AS

BEGIN

EXEC('Select first, last, address, address2, city, state, zip, county, [Home Phone],
      [Work Phone], [Mobile Phone],
      order, description
      FROM vwAllOrders
      WHERE first = ' + @FirstName
)
END



--EXECUTION
EXEC spSearchFirstName @FirstName = N'Thomas '
Avatar of Snarf0001
Snarf0001
Flag of Canada image
Don't add the '+ to the clause, the variable will take the place of a literal string value:

ALTER PROCEDURE [dbo].[spSearchFirstName]
       @FirstName varchar(155) 
 AS

 BEGIN

 EXEC('Select first, last, address, address2, city, state, zip, county, [Home Phone],
       [Work Phone], [Mobile Phone], 
       order, description
       FROM vwAllOrders
       WHERE first = @FirstName 
 )
 END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gwarcher
gwarcher

ASKER

Thanks, force of habit using EXEC.