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.

6 Years
Discussion Span
Last Post by debasisdas

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

Edited by crunchie: n/a


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;
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.