CODEWITHSUNDEEP
sqlsql-server-2005stored-procedures
Benk
Benk

Reputation: 1312

SQL Server 2005 WHERE Clause with like condition

I need to build a WHERE clause based on the parameter that is been passed into a stored procedure.

For example: if param1 value is 1 then do like condition, 

 @param1 like '%'+ product.Status + '%'

else I want to ignore the like condition and it would return all rows....

How would I do it in the WHERE clause? Thanks,

Upvotes: 0

Views: 1915

Answers (5)

1234
1234

Reputation: 21

@param1 like ISNULL('%'+ product.Status + '%', '%')

Upvotes: 2

HABO
HABO

Reputation: 15852

The like operator needs two parameters:

where ( @Param1 = 1 and product.Status like '%' + @Pattern + '%' ) or
  ( @Param1 = 2 and product.Voltage = 240 ) or
  ( @Param1 is NULL )

Upvotes: 0

Tim Lehner
Tim Lehner

Reputation: 15251

My guess from your description (not code) is that you want a query like the following:

select *
from product
where ([Status] like '%' + @param1 + '%'
    or @param1 is null)

This will return all products that wildcard-match Status on @param1 or return all rows if @param1 is null.

I've used parenthesis in the event that you add more to your where clause.

Upvotes: 0

Alberto De Caro
Alberto De Caro

Reputation: 5213

SO you need to alter the where clause depending on a parameter. Use sp_executesql: you can build the query as a string, so you can add the where clause if @param = 1:

declare @sql varchar(1024)
declare @where varchar(1024)
set @sql = N'SELECT field1, field2, field3 FROM myTable'
set @where = N' WHERE columnName like '%'+ product.Status + '%''
case when @param = 1 then concat(@sql,@where)
execute sp_executesql @sql

Upvotes: 0

John Woo
John Woo

Reputation: 263843

You have to use IF Condition. Try something like this.

if @param1 = 1
    Begin
        select * from tableName WHERE columnName like '%'+ product.Status + '%' 
    End
else
    Begin
        select * from tableName WHERE columnName
    End

Upvotes: 1

Related Questions