A 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..

sp_executesql has the ability to accept typed parameters and it is this that reduces the risk of injection significantly. But it has a quirky behaviour that we can leverage for our free range SQL. The second parameter for this proc is a string that takes a combination of parameter name and types.
[@params =] N'@parameter_name  data_type [,…n]' }
The quirky bit is that the parameters declared do not have to exist in the underlying SQL, but the reverse is not true. This behaviour also applies for the third parameter which is a parameter array.. We can use this behaviour to our advantage….

Each column in SQL is given a unique ordinal position when the table is created. Although relational redundant, this can come in handy…Using the POWER function in TSQL we can compute the bit wise integer for each column….

SELECT COLUMN_NAME, POWER(2,ORDINAL_POSITION) as BitWise
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= 'Employees'

Now that we have these values, we can create a WHERE clause that covers every possible combination requested.

Using the Northwind database, focusing on the Employees table and its first 5 columns, the stored procedure below outlines the technique..

CREATE PROC upEmployeesSearch
@EmployeeID INT = null,
@LastName NVARCHAR(40) = null,
@FirstName NVARCHAR(20) = null,
@Title NVARCHAR(60) = null,
@TitleOfCourtesy NVARCHAR(50) = null
AS
SET NOCOUNT ON
DECLARE @BITMASK INT
DECLARE @WHERE NVARCHAR(4000)
SELECT @BITMASK = 0, @WHERE =''
–Build the BitWise value
IF @EmployeeID IS NOT NULL SET @BITMASK = @BITMASK + 2
IF @LastName IS NOT NULL SET @BITMASK = @BITMASK + 4
IF @FirstName IS NOT NULL SET @BITMASK = @BITMASK + 8
IF @Title IS NOT NULL SET @BITMASK = @BITMASK + 16
IF @TitleOfCourtesy IS NOT NULL SET @BITMASK = @BITMASK + 32
–Build the Clause filtered by the bitwise expression
SELECT @WHERE = COLUMN_NAME + ' = @' + COLUMN_NAME + ' AND ' + @WHERE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= 'Employees' AND @BITMASK = POWER(2,ORDINAL_POSITION) | @BITMASK
ORDER BY ORDINAL_POSITION DESC
–Complete the Where expression
IF LEN(@WHERE) > 0
SET @WHERE = ' WHERE ' + LEFT(@WHERE,LEN(@WHERE)-4)
–Final SELECT
SET @WHERE = 'SELECT * FROM Employees' + @Where
–PRINT @WHERE
–Notice that every single parameter is declared….
EXEC sp_executesql @WHERE, N'@EmployeeID int, @LastName nvarchar(40),
@FirstName nvarchar(20), @Title nvarchar(60), @TitleOfCourtesy nvarchar(50)',
@EmployeeID = @EmployeeID, @LastName = @LastName, @FirstName = @FirstName,
@Title = @Title, @TitleOfCourtesy = @TitleOfCourtesy
go
–Examples
exec upEmployeesSearch 3, 'Leverling', 'JANET', NULL, 'Ms.'
exec upEmployeesSearch NULL, NULL, NULL, NULL, 'Ms.'
exec upEmployeesSearch NULL, NULL, NULL, 'Sales Representative', 'Ms.'
exec upEmployeesSearch NULL, 'Leverling'
exec upEmployeesSearch

It looks like a lot of typing but is far less than others, it is relatively easy to script out the arguments, the bit wise IF's and the arguments for sp_executesql.

The use of the INFORMATION_SCHEMA view used in this example can (and should) be replaced by a custom table that holds the relevant bit wise values, columns names and operators (if you are using more than equality).
Expand the custom table setup to also contain the SELECT expression then create a UDF that returns the entire expression and you have a framework to use across multiple queries with minimal work.

EDIT: I am getting some feedback regarding the overall performance of the entire procedure including the bitwise table work. So in the interests of a true comparison, below is a simple schema for the BITWISE Table and the altered statment that should be replaced.

–DDL
CREATE TABLE [BITWISE] ([TABLE_NAME] [sysname] NOT NULL , [COLUMN_NAME] [sysname] NOT NULL ,
[BitWise] [int] NOT NULL, PRIMARY KEY(TABLE_NAME, COLUMN_NAME))
GO
–DML
INSERT BITWISE
SELECT 'Employees' AS TABLE_NAME, COLUMN_NAME, POWER(2,ORDINAL_POSITION) as BitWise
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= 'Employees'
go –Replace the INFORMATION_SCHEMA view work with this in the above procedure…
SELECT @WHERE = COLUMN_NAME + ' = @' + COLUMN_NAME + ' AND ' + @WHERE
FROM BITWISE
WHERE TABLE_NAME= 'Employees' AND @BITMASK = BITWISE | @BITMASK