Ignoring blank input parameter in a stored procedure

[noah_britton]

I'm trying to write a SP that ignores a blank parameter in a where clause like
this.
create procedure blah @myvar int AS
select * from companies
where 1 = 1
if @myvar is <> ''
and id = @myvar

This seems like a simple task but it doesn't work. Thanks in advance

[paross1]

Assumint SQL Server, try something like this:

SELECT *
FROM companies
WHERE id = ISNULL(@myvar, id)

If your @myvar parameter is NULL, then your WHERE clause is basically <b>id =
id</b>, which makes it a "don't care" condition and returns all rows as if
there was no where clause.

Phil

[noah_britton]

Yes, I'm working with SQL Server. The code you posted tworks if the parameter is Null, but what if it is passed as a blank string?
Thanks,
Noah

[paross1]

Try it....shouldn't make any difference.

[MikerRoo]

Use the ISNULL trick but at the top of your stored procedure add the line:
<b>IF LTRIM (@myvar) = '' SET @myvar= NULL</b>