 <p>It looks like you are trying to make a pretty generic search stored procedure with paging. These are difficult to implement properly in t-sql only, and can become maintenance headaches down the road due to the branching logic, or additional supporting stored procedures you need to add...</p> <p>I would start to look at other options outside of a pure sql approach. Using an orm, or micro orm could help a lot. Actually, take a look at what Sam Saffron came up with...</p> <p><a href="http://samsaffron.com/archive/2011/09/05/Digging+ourselves+out+of+the+mess+Linq-2-SQL+created" rel="nofollow">http://samsaffron.com/archive/2011/09/05/Digging+ourselves+out+of+the+mess+Linq-2-SQL+created</a></p>