How to append Queries in Stored Procedure based on condition

Vishwas Kadamannaya

15y4.7k

How could I write this in sql stored procedure?

Hi All, 
      I need to append my queries based on the condition how could i do this in stored procedure


StringBuilder query = new StringBuilder();    
**query.AppendLine("Select * from xxx Where 1 =1");**

if(Id != 0)    
query.AppendLine("And Id = @Id");

if(Nemae != null)    
query.AppendLine("And Name = @Name");


Thanks in Advance..

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suthish Nair15y ago
yep.. Raja is right...
0
Raja Krishnamurthy15y ago
Try using optional parameters technique. In your stored procedure have both the Id and Name parameters and do the following in your query

SELECT
    Id,
    Name
FROM
    table1
WHERE
(Id = @id or @Id = 0) --Either Id matches the given id or the given Id is 0
And (Name = @Name or @Name='') --Either the Name matches the given name or name is null

HTH.

Happy Programming!!!

Regards,
Raja
0