Using Dynamic SQL Statements in Stored Procedures

DECLARE @SQL varchar(1000)
SET @SQL = 'SELECT Cus_Name, Cus_City, Cus_Country FROM Customers '
IF @Cus_Name IS NOT NULL OR @Cus_City IS NOT NULL OR _
@Cus_Country IS NOT NULL
SET @SQL = @SQL + 'WHERE '
IF @Cus_Name IS NOT NULL
SET @SQL = @SQL + 'Cus_Name = ' + @Cus_Name
...
EXEC(@SQL)

Using Dynamic SQL Statements in Stored Procedures 
http://www.4guysfromrolla.com/webtech/102300-1.shtml

http://www.sqlteam.com/item.asp?ItemID=2077