dynamic where clause using stored procedure

[AngelicOne]

I have a table that contains ID, lastname and firstname of a person. How to make my stored procedure handle the where conditions using only stored procedure.

SELECT * FROM person where lastname = @lastname

SELECT * FROM person where firstname = @firstname

SELECT * FROM person where ID = @id

Instead of making three stored procedure, I need to do it entirely in one sp.

[darkagn]

The SQL WHERE clause allows boolean logic operators AND and OR. You can combine your statement into one like so:

SELECT * FROM person WHERE lastname = @lastname AND firstname = @firstname AND ID = @id

[debasisdas]

When you say, i consider you want to run the query dynamically on different field names.

If that is the case then, you need to maintain a flag, along with the input parameter and based on the value of the flag frame the query dynamically and execute.

if p_flag=1 then
SELECT * FROM person where lastname = @p_val;
else if p_flag=2 then
SELECT * FROM person where firstname = @p_val;
.......
......
end if;