Dynamic SQL
Part three of a four-part series of blogs

Dynamic SQL is a technique for building valid SQL statements from separate pieces of text. You can use this technique to create remarkably flexible and useful queries, as long as you're aware of the potential danger of SQL injection attacks.

  1. Dynamic SQL
  2. Concatenation in SQL
  3. Building Dynamic SQL Statements (this blog)
  4. SQL Injection Attacks

This blog is part of our complete SQL tutorial. You can also learn about this technique from our SQL Server training courses.

Posted by Andrew Gould on 22 April 2013

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.

Building Dynamic SQL Statements

The most useful method for building dynamic SQL is to create a stored procedure which accepts parameters. This part of the series explains how to create some basic stored procedures to generate and execute dynamic SQL statements.

Selecting from any Table

For our first example we'll create a stored procedure which will allow a user to pass in the name of any table via a parameter and which will return the first ten records from that table.

CREATE PROC spTopTenFromTable

(

@TableName VARCHAR(128)

)

AS

BEGIN

DECLARE @SQLString VARCHAR(MAX)

SET @SQLString =

'SELECT TOP 10 * FROM ' + @TableName

EXEC (@SQLString)

END

You could also create the same procedure using the sp_executesql stored procedure.

CREATE PROC spTopTenFromTable

(

@TableName NVARCHAR(128)

)

AS

BEGIN

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString =

N'SELECT TOP 10 * FROM ' + @TableName

EXEC sp_executesql @SQLString

END

Once you've created the stored procedure you can then call it and pass in the name of any table, as shown below:

EXEC spTopTenFromTable 'tblActor'

EXEC spTopTenFromTable 'tblFilm'

EXEC spTopTenFromTable 'tblDirector'

Each call to the stored procedure selects the first ten records from the specified table.

Choosing the Number of Records to Display

We can extend the above example by adding a second parameter which allows users to specify how many records they want to display.

CREATE PROC spTopFromTable

(

@TableName NVARCHAR(128)

,@NumRecords INT

)

AS

BEGIN

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString =

N'SELECT TOP ' +

CAST(@NumRecords AS VARCHAR(4)) +

' * FROM ' + @TableName

EXEC sp_executesql @SQLString

END

Once the procedure has been created you can call it and specify how many records you want from whichever table.

EXEC spTopFromTable 'tblActor', 5

EXEC spTopFromTable 'tblFilm', 20

EXEC spTopFromTable 'tblDirector', 3

Some sample output from executing these three lines is shown below:

Results of stored procedure

Each call to the stored procedure returns a different number of records from a different table.

 

Dynamic Criteria

You can use dynamic SQL to make the WHERE clause of a statement variable. The example below creates a procedure which allows any word to be passed in to the criteria of a simple query:

CREATE PROC spFilmsCalled

(

@FilmTitle NVARCHAR(MAX)

)

AS

BEGIN

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString =

N'SELECT *

FROM tblFilm

WHERE FilmName = '''

+ @FilmTitle + ''''

EXEC sp_executesql @SQLString

END

Notice the use of multiple single quotes to concatenate the quotes that will enclose the word passed in via the parameter. Once the procedure has been created you can call it to show any film whose title matches the word you have asked for:

EXEC spFilmsCalled 'King Kong'

The output from this code is shown below:

Three films matching results

There are three films whose names are exactly equal to the text passed into the stored procedure.

 

We can make the procedure somewhat more flexible by using the LIKE operator along with some wildcards:

CREATE PROC spFilmsLike

(

@FilmTitle NVARCHAR(MAX)

)

AS

BEGIN

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString =

N'SELECT *

FROM tblFilm

WHERE FilmName LIKE ''%'

+ @FilmTitle + '%'''

EXEC sp_executesql @SQLString

END

Now, rather than having to pass in an exact film title you can search for films whose name contains any word:

EXEC spFilmsLike 'King'

The output from the code above is:

Output from flexible query

Any film whose name contains the word King appears in the results.

 

It's not actually necessary to use dynamic SQL to create flexible criteria in a query. You can use basic stored procedure parameters to achieve the same result.

Using the IN Operator

Although you can create basic dynamic criteria using simple parameters, you can't easily use this technique with the IN operator.  This operator accepts a comma-separated list of values, as shown below:

SELECT

*

FROM

tblFilm

WHERE

FilmRunTimeMinutes IN (90,120,150,180)

You can't use a simple parameter to set the input for the IN operator, but you could use dynamic SQL to do it:

CREATE PROC spFilmLengths

(

@FilmLengthList NVARCHAR(MAX)

)

AS

BEGIN

DECLARE @SQLString NVARCHAR(MAX)

SET @SQLString =

N'SELECT *'

FROM tblFilm

WHERE FilmRunTimeMinutes IN ('

+ @FilmLengthList + ')'

EXEC sp_executesql @SQLString

END

You can now call the procedure passing in as many different values as you like within a single string:

EXEC spFilmLengths '100,110,120,130'

Using Parameters with sp_executesql

We've already mentioned that using the sp_executesql stored procedure can make your dynamic SQL queries faster because they can reuse a cached query plan whereas the simple EXEC statement can't. Another major difference between the two methods is that sp_executesql has extra optional parameters which allow you to set the criteria of a dynamic SQL query.

The basic syntax of the argument list is shown below:

EXEC sp_executesql SQLStatement, ListOfParameters, ListOfParameterValues

As an example we can create a single parameter for a simple SQL statement like so:

EXEC sp_executesql

N'SELECT * FROM tblFilm WHERE FilmRunTimeMinutes = @Length'

,N'@Length INT'

,@Length = 120

In the code above the first argument contains the SQL statement that we are executing as a unicode string and includes a reference to the parameter called @Length. The second argument contains the definition of the @Length parameter as another unicode string. The final argument sets the value of the parameter that we have defined.  Executing the above code will show all of the films whose running times are 120 minutes.

We can use multiple parameters with this technique, as shown in the code below:

EXEC sp_executesql

N'SELECT * FROM tblFilm

WHERE FilmRunTimeMinutes = @Length

AND FilmReleaseDate > @StartDate'

,N'@Length INT, @StartDate DATETIME'

,@Length = 120

,@StartDate = '2000-01-01'

Again, the first argument defines the complete SQL statement and the second argument defines the parameters that are referenced within the SQL statement. The subsequent arguments are then used to set the values of parameters that have been defined.

What's Next?

Now that you've seen several ways to use dynamic SQL it's time to point out one of the hidden dangers. Read the next part of this series to learn about the dreaded SQL injection attack.

  1. Dynamic SQL
  2. Concatenation in SQL
  3. Building Dynamic SQL Statements (this blog)
  4. SQL Injection Attacks
This blog has 0 threads Add post