if else with WHERE

Hi,
I'm trying to create a store proc and use a WHERE clause only if a certain
condition is met. When I use the WHERE after an if condition, the sql
enterprise tool tells me
it's invalid. "Invalid syntax near WHERE clause"
Here is my proc:
CREATE PROCEDURE [dbo].GetContractorsList
@.characterFilter char(1)
AS
SELECT
[ContractorCode],
[ContractorName],
FROM
[dbo].[Contractors]
if @.characterFilter !='*'
begin
WHERE ContractorName LIKE @.characterFilter + '%'
end
Any ideas on how to do this?
Thanks,
OpaOpa wrote:
> Hi,
> I'm trying to create a store proc and use a WHERE clause only if a
> certain condition is met. When I use the WHERE after an if
> condition, the sql enterprise tool tells me
> it's invalid. "Invalid syntax near WHERE clause"
>
http://www.sommarskog.se/dyn-search.html
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks for your reply Bob.
The article you posted is quite lengthy and refers to Dynamic SQL.
Can I do this without it?
"Bob Barrows [MVP]" wrote:

> Opa wrote:
> http://www.sommarskog.se/dyn-search.html
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||Yes, one of the methods described in the article should suit your purpose:
WHERE (@.characterFilter != '*' OR ContractorName LIKE @.characterFilter +
'%')
I suggest you read the entire article. It is quite informative.
Opa wrote:
> Thanks for your reply Bob.
> The article you posted is quite lengthy and refers to Dynamic SQL.
> Can I do this without it?
>
> "Bob Barrows [MVP]" wrote:
>
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"