Dynamic Where Clauses

[codingholic]

I have a table like the following:

CREATE TABLE Customers
(
CustomerCode varchar(20) not null, Surname varchar(60) not null,
Firstname varchar(60) null, Salutation varchar(60) null,
CustomerName varchar(182) null, DateOfOldestOpenItem datetime null
)

and what I want is to have a SProc to do a search... conceptially as following:

CREATE PROC usp_CustomerNameSearch
@SearchType varchar(10),
@SearchValue varchar(182)
AS

SELECT *
FROM Customers
WHERE
-- CASE WHEN @SearchType = 'FN' THEN Firstname = @SearchValue
-- ELSE CustomerName = @SearchValue END

I know its the wrong syntax but CASE doesn't work against fields in where clauses.

Any suggestion I can achieve the same result?

(NOTE: create a large varchar variable to dynamically produce the query is NOT an option in my case unfortunately)

[ck9663]

I have a table like the following:

CREATE TABLE Customers
(
CustomerCode varchar(20) not null, Surname varchar(60) not null,
Firstname varchar(60) null, Salutation varchar(60) null,
CustomerName varchar(182) null, DateOfOldestOpenItem datetime null
)

and what I want is to have a SProc to do a search... conceptially as following:

CREATE PROC usp_CustomerNameSearch
@SearchType varchar(10),
@SearchValue varchar(182)
AS

SELECT *
FROM Customers
WHERE
-- CASE WHEN @SearchType = 'FN' THEN Firstname = @SearchValue
-- ELSE CustomerName = @SearchValue END

I know its the wrong syntax but CASE doesn't work against fields in where clauses.

Any suggestion I can achieve the same result?

(NOTE: create a large varchar variable to dynamically produce the query is NOT an option in my case unfortunately)

if you need the entire table returned to you, i think a table-function is your best choice. try something like:

 
select * from 
(SELECT 'F' as key, * 
FROM Customers
WHERE Firstname = @SearchValue 
union 
SELECT 'C' as key, * 
FROM Customers
WHERE CustomerName = @SearchValue)  mysearch
where key = substring(@SearchType,1,1)
 
 

or you can build your query in a string and do and EXEC (@sqlstring)

[codingholic]

I actually don't need to return the entire table. I was trying to make it simpler to read and to be answer.

The actual script is about 400 lines with lots of subquery inside.

So both of the option you suggest will work, but will be lots and lots of work.

I just thought its the easiest if I just need to change the lines in question :)