Dynamic Select columns using SQL Server Stored Procedure
Posted on 2009-04-02
I have to write a single stored procedure that returns data to a client application that allows users to build dynamic queries based on a variable mix of selection parameters. The application presents the user with a check list of field names and the user ticks the fields which are to be included in the report, e.g.
[ ] Product Code
[ ] Product Description
[ ] Weight
[ ] Volume
[ ] Standard Price
[ ] Supplier
(There are upwards of 100 different fields that the user may choose.)
Somehow I need to be able to pass the list of column names to a stored procedure and build the sql select statement within the stored procedure based on which columns have been selected by the user. I also have to do it in a way that does not make the stored procedure wide open to an injection attack.
What's the best way of doing this?