Avoid Dynamic Query

Avoid Dynamic queries, Use the alternate mentioned below:
Here the business logic is that the parameter @Param
Can be 'ALL' or a given value. In case of All we don’t need to filter on ColumnName where as otherwise we have to filter out based on parameter.

[code language="sql"]
DECLARE @Query VARCHAR(MAX), @Param VARCHAR(10)
SET @Param = 'ALL'
SELECT @Query = 'SELECT * FROM tblName '
IF  @Param <>'ALL'
BEGIN
SELECT @Query = @Query + 'WHERE ColumnName = ' + @Param
END
EXEC @Param
[/code]

Above dynamic query can be replaced by the following code.

[code language="sql"]
DECLARE @Query VARCHAR(MAX), @Param VARCHAR(10)
SET @Param = 'ALL'
SELECT * FROM tblName
WHERE (@Param = CASE WHEN @Param = 'ALL' THEN @Param
ELSE ColumnName END) -- All\Specific
[/code]