If you want the sproc to return all records or selected records you can do something like this.

GetXs:

@Xid int
AS
Select * FROM Xs WHERE (@Xid = 0 And Xid > 0) or Xid = @Xid

(@Xid = 0 And Xid > 0): If Xid parameter is 0 then you want all records. SQL sees this as (0=0 And Xid >0) so if @Xid is not 0 the whole first statement returns false for each record so no records are returned

Xid = @Xid:  This statement get the records based on the specific ID.  If the first statement is true, this really has not effect since all records were selected in the first statement.  If the first statement is false (AKA: @Xid != 0), the records are only selected for the specifc Xid.

I am writing this from memory, so if it does not work let me know and I will double check the syntax / where statement.  I do not have access to my development machine at this time .

The nice thing is if you have multiple parameters you can use the same logic, just have multiple where clauses like the one above.

@Xid int
AS
Select * FROM Xs

WHERE (@Xid = 0 And Xid > 0) or Xid = @Xid

AND (@MyID = 0 And MyID >0) or MyId = @MyID

The bad thing is I am working on a project for a company and just wrote a selectAll SPROC and was going to write another SPROC for the searches that would be available.  I forgot all about this until I saw this post.  Get go refactor my sproc.