There are many times when you might be concatenating text in a stored procedure to make a where clause, because the input parameters are optional. An alternative is to create many versions of the stored procedure - one for each possible combination of parameters - which can soon become too big. There is a more viable alternative - use "lazy evaluation". In the following script, @Input mimics an input parameter. If it is the default (-1) then all records are returned, while other values of @Input are added to the where clause as normal.
A short but interesting article, the author has figured out a way to create a PDF from a stored procedure without using a third party library.