using variable as WHERE clause

[MCOOP]

Is there a way to have a declare variable (@where) be used as the WHERE clause in a stored procedure?

The reason I ask is that I'm trying to use one sql statement for several
possible WHERE conditions. The WHERE could be looking for Lastname or an account number.

example:
create procedure selCustomerSear ch @Acct int = null
, @Lname varchar(20) = null
AS

declare @where varchar(100)

if @Acct is not null
set @where = 'acctnum = ' + @Acct
else
set @where = 'lname = ' + @Lname (@Lname is passed into storedproc)


SELECT *
FROM Customer
WHERE @where

[iburyak]

Replace

SELECT *
FROM Customer
WHERE @where

With:

Declare @SQL varchar(8000)

Select @SQL = 'SELECT *
FROM Customer
WHERE ' + @where

Exec(@SQL)

[Taftheman]

Try using a case statement instead