T-SQL: Dynamic Query by Selected Column in ASP.NET GridView
- by jp2code
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?
