SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Avoid Dynamic Query

on April 29, 2013

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.

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

Above dynamic query can be replaced by the following code.

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: