Member Avatar for 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.

  • 3 Contributors
  • 2 Replies
  • 223 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by debasisdas
Member Avatar for 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
Edited by crunchie because: n/a
Member Avatar for 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;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.