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 tutorial to learn about the dreaded SQL injection attack.
