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:
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:
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:
The output from the code above is:
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.
