Use of Parameter or Variable in Where Clause

[Amesville]

Hi again, probably a dumb question but here goes,

I'm doing a fairly simple query which uses a like operator in a where clause, kind of like the following:

WHERE Available LIKE 'O/L%'

But I'd like to be able to alter the caluse depending on the value of a parameter passed in, so I could change the clause to either LIKE ot NOT LIKE <value%>

I tried this but it didn't work:

WHERE Available @Parameter1 LIKE 'O/L%'

and I passed in either 'NOT' or ' as @Parameter1.

So I was wondering if there is a way of using an if - then statement in the Where clause that I could use to do something like this:

IF @Parameter1 = 'LIKE' THEN
WHERE Available LIKE 'O/L%'
ELSE
WHERE Available NOT LIKE 'O/L%'
END IF

I'm thinking this probably won't work either and that the answer is going to be Dynamic SQL (Which I have NO idea yet how to do). But any suggestions to help with a solution would be nice.

Thanks

Craig

Amesville

 

[SQLBill]

You would have to use dynamic SQL. It would be something like:

DECLARE @sqlcmd VARCHAR(1000)
DECLARE @myvar VARCHAR(20)

SET @myvar = 'LIKE '0/L%''

SET @sqlcmd = 'SELECT col1 FROM tableA WHERE col1 ' + @myvar

PRINT @sqlcmd
--EXEC(@sqlcmd)

You run that and it won't execute, it will display what it would run. That way you can debug it if needed. Once you have it doing what you want, you comment out the PRINT statement and uncomment the EXEC. You can turn it into a stored procedure and then you can pass the variable into it.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Crowley16]

just to answer your original question, there is a if / else syntax in t-sql, it would go something like:

if (@Parameter = 1)
begin
select something...
end
else if (@Parameter = 2)
begin
select something else...
end
else
begin
select everything else...
end

in your situation, it'd be easier to use dynamic sql, however there are reasons to avoid dynamic sql, which I won't go into as it'll be too big a topic, but something you might want to look into.

--------------------
Procrastinate Now!

 

[PWise]

no need for dynamic sql

I would use

Select ....
From Tablename
WHERE Available LIKE 'O/L%'
And @Parameter1 = 'LIKE'
Union
Select ....
From Tablename
WHERE Available not LIKE 'O/L%'
And @Parameter1 <> 'LIKE'