Dynamic SQL - No, No, No!
Dynamic SQL should only be used as a last resort. This article gives you a tip for avoiding the use of dynamic SQL in WHERE clauses

Posted by David Wakefield on 02 July 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Dynamic SQL No, No, No!

I am not going to tell you the dangers of dynamic SQL (there are hundreds of articles on the internet explaining the problem of SQL Injection attacks). This article gives you a technique to allow you to avoid dynamic SQL where it is misused the most, the WHERE clause.

The problem

It is very often the case that you need to present a form for your users which allows them to select data by multiple disparate criteria:

Criteria form

This form allows you to search for pop stars by various criteria

In the form above we may just want to see all Black, Female pop stars or maybe we want to find all pop stars whose last name is Osmand. The problem for the programmer is writing code which allows us to use only those parts of the selection criteria the user is interested in.

The Dynamic SQL solution

The following code constructs an SQL string by concatenating only the conditions where the user has made a selection:

Create Proc spSelectPopStar

@FirstName as varchar(Max) = ''

,@LastName as varchar(Max) = ''

,@Sex as varchar(Max) = ''

,@Race as varchar(Max) = ''

,@DOBFrom as Datetime

,@DOBTo as Datetime



declare @SQLStr as varchar(Max)

-- start the SQL

set @SQLStr = 'SELECT * FROM tblPopstar

WHERE 1=1 '

-- concatenate only those conditions required

if @FirstName <> ''


set @SQLStr = @SQLStr +

' and firstname like ''%' + @FirstName + '%'''


if @LastName <> ''


set @SQLStr = @SQLStr +

' and lastname like ''%' + @LastName + '%'''


if @Sex <> ''


set @SQLStr = @SQLStr + ' and sex=''' + @Sex + ''''


if @Race <> ''


set @SQLStr = @SQLStr + ' and race=''' + @Race + ''''


if @DOBFrom is not null


set @SQLStr = @SQLStr + ' and dob>''' + @DOBFrom + ''''


if @DOBTo is not null


set @SQLStr = @SQLStr + ' and dob<> + @DOBTo + ''''


-- run the built up string

exec (@SQLStr)

The above would work fine. The only clever bit is WHERE 1=1 this means that even if the user chooses nothing we still have correctly syntaxed SQL, and we don't have to write complicated code to determine when (and when not) to concatenate in an AND.

The problem is that the above code leaves us vulnerable to an SQL Injection attack - e.g. the user typing:  

''; DELETE FROM tblPopStart WHERE 'a%'='a

in the Last Name search box.

The better solution using default parameters

The following uses default parameter values to substitute in values which ensure that if the user has typed nothing in the criteria box, the SELECT statement will select all records.

Create Proc spSelectPopStar

-- provide defaults which we can test to determine

-- if the user has typed anything in

@FirstName as varchar(Max) = ''

,@LastName as varchar(Max) = ''

,@Sex as varchar(Max) = ''

,@Race as varchar(Max) = ''

,@DOBFrom as Datetime = '01 Jan 1753'

,@DOBTo as Datetime = '31 Dec 2100'


-- Parameters may be populated with NULLS so replace

-- with our well thought out defaults

set @FirstName = isnull(@FirstName,'')

set @LastName = isnull(@LastName,'')

set @Sex = isnull(@Sex,'')

set @Race = isnull(@Race,'')

set @DOBFrom = isnull(@DOBFrom,'01 Jan 1753')

set @DOBTo = isnull(@DOBTo,'31 Dec 2100')

-- compare fields against parameters

-- OR whether the parameter is the default value

SELECT * FROM tblPopstar


(FirstName like '%' @FirstName '%') -- Blank value selects all

and (LastName like '%' @LastName '%') -- Blank value selects all

and ((Sex = @Sex) or (@Sex = '')) -- Blank value selects all

and ((race = @Race) or (@Race = ''))-- Blank value selects all

and (DOB > @DOBFrom)  -- all popstars were born after 1753

and (DOB < @dobto)="">-- All popstars are born before 2100

The most interesting lines are the Sex and Race lines. The OR operator means that if the user leaves @Sex blank, every record will be selected because @Sex='' even though the column Sex will always be male or female.

The fact that every name matches LIKE '%%' means that a blank @FirstName or @LastName will select all records.

The fact that for the next 88 years we can assume all pop stars are born between 1753 and 2100 means the last two lines will select all records, if @DOBFrom and @DOBTo are left blank.


It is always possible to construct a WHERE clause without resorting to Dynamic SQL.

This blog has 0 threads Add post