Appending query based on condition in sql stored procedure

In my .net 2.0 application I want to convert all the inline queries to sql stored procedures.

I have queries like

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

if(Id != 0)    
query.AppendL开发者_C百科ine("And Id = @Id");

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

How could I write this in sql stored procedure?

Do I need to repeat?

You can pass parameters to stored procedures and use them like this:

CREATE PROC spTest
(
    @name varchar(30)=NULL
    , @id int=NULL
)
AS
BEGIN
    SELECT * FROM TABLE_NAME
    WHERE (@name IS NULL OR name = @name)
    AND    (@id IS NULL OR id = @id)
END

Probably like this :

SELECT * FROM TABLEA
WHERE (@PARAM1 = 0 OR ID=@PARAM1) AND (@Name = NULL OR COLUMN2=Name)