Filtering Procedures.. Do you make it right?

All of us do filtering in their project. It's one of the most repeated functionalities. But do we make it well? How do you make your filtering procedures? Ok.. Have a look on the following one:

CREATE PROCEDURE std_GetFiles
@fileCategory varchar(50),
@tag varchar(50)
AS
declare @SQL as nvarchar(200)

SET @sql = 'SELECT * FROM tscoFileIndex WHERE '

if @fileCategory <> null
set @sql = @sql + ' FileCategory = ' + @fileCategory + ' and '

if @tag <> null
set @sql = @sql + ' tag = ' + @tag + ' and '

SET @SQL= LEFT(@SQL, LEN(@SQL) -4)

GO

This is the first way come to your mind when you trying to do filtering. But take care. Actually, all the string concatenation in this procedure make your data under threaten. As this allow procedure users to inject sql statements inside your concatenated query by passing unexpected paramaters through "fileName" and "tag" inputs. Also the many if conditions here affects your procedure performance.

The alternative, which is better than this, would be something like that:

CREATE PROCEDURE std_GetFiles
@fileCategory varchar(50),
@tag varchar(50)
AS

SELECT *
FROM tscoFileIndex
WHERE (@fileCategory is null OR FileCategory = @fileCategory ) AND (@tag is null OR tag = @tag)

GO

So, you match all rows if the parameter is null, and use the
condition when the parameter is not null.