Free Range SQL - It tastes better.

Free Range SQL - It tastes better.

Interesting approach to flexible queries without giving up stored procedure usage...

" common requirement from users is the ability to be able to search on the columns they want.
In affect they would like to roll there own queries.
As an example take the Employees table in Northwind. The user wants to be able to search on any/all fields.
If your development environment only allows stored procedures then the usual solution is to use the ISNULL or COALESCE for each column in the WHERE clause.
SELECT * from Employees WHERE EmployeeID = ISNULL(@EmployeeID, EmployeeID)

Problem is, performance sucks..At best you will get an index scan...
There are some ways to build the predicate that can make use of index efficiently depending on the data type, but in most cases, the column needs to be NOT NULL. Some techniques

The other solution is dynamic SQL. This raises the stress on security ,specifically injection, compared to the type parameters used in Stored Procedures.
But by bringing together sp_executesql, column ordinal positions, some bit wise arithmetic and finally a standard naming convention, we can overcome the security and performance issues while satisfying the users requirements.."