I'm trying to modify a stored procedure used in an ASP.NET page.

By default, the stored procedure returns all of the data, which can be overwhelming for employees in the plant.

I want to add a drop down menu item for the column name and a text box for a value to allow our employees to search the data for their specific items.

What I would like to add is the ability to pass in a Column Name and Column Value to search, similar to the following:

DECLARE @colName nVarChar(50), @colValue nVarChar(50)
SET @colName='EmployeeID'
SET @colValue='007135'
SELECT  Column1, Column2, Column3, Column4, Column5, Column6, Column7
FROM    viewNum1
WHERE   ((@colName IS NULL) OR (@colValue IS NULL) OR ('['+@colName+']'=@colValue))

If all values passed in (@colValue and @colName), all records return; however, if I try specifying that @colName=EmployeeID and @colValue='007135' (a value that does exist in the database), no records are returned.

Next is the problem that I am running an old SQL Server 2000 database that does not allow the stored procedure to access the table column names, and the whole technique looks prone to SQL Injection.

Finally, I don't see how to bind my GridView control to this and still have the ability to display all records.

How would I write such a filtering stored procedure?