|
|
Dynamic queries are queries that are non-deterministic. In other words, the actual query run, or the number of columns returned, are not known at design time but are determined by parameters at run time.
This is made possible by the ability of the EXECUTE statement and a system stored procedure called sp_executesql to execute scripts from strings.
For example, you can get the same result from
SELECT * FROM [Order Details]
by running the following
EXECUTE ('SELECT * FROM [Order Details]')
The difference is that with the EXECUTE statement, the script is in a string that we can programmatically manipulate. Consider the following:
DECLARE @SQLSCRIPT as nvarchar(1000) DECLARE @TableName as nvarchar(100) SET @SQLSCRIPT = 'SELECT * FROM ' SET @TableName = '[Order Details]' EXECUTE (@SQLSCRIPT + @TableName)
| Note |
Spaces in the string that you use are important. |
By simply changing the value of @TableName to Orders, we can do a select on the Orders table.
DECLARE @SQLSCRIPT as nvarchar(1000) DECLARE @TableName as nvarchar(100) SET @SQLSCRIPT = 'SELECT * FROM ' SET @TableName = 'Orders' EXECUTE (@SQLSCRIPT + @TableName)
This is all very fine and great, but the EXECUTE statement does have certain limitations. One of the major limitations is that the script in use cannot return any value. To overcome this, you have to use sp_executesql.
sp_executesql has a simple syntax:
sp_executesql <Script>, [ <Parameter list> , Parameter1, n...]
<Script>: A string that contains the script you want to execute
<Parameter list>: A string that contains the declaration of parameters used in <Script>
Parameter1, n…: The parameters you want to pass in <Script> and/or the return value from <Script>
Consider the following situation. Let’s assume that you need a stored procedure to return the rows for a given table name, a column name, and a search value. Let’s call this stored procedure sp_getRows. The script for sp_getRows is shown in Listings D-9a to D-9c.
The first step, as usual, is to declare the stored procedure and variable required.
 |
if exists
(select * from dbo.sysobjects
where id = object_id(N'sp_GetRows') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure sp_GetRows
GO
create procedure sp_GetRows
/******************************************
Procedure: sp_UpdateContactInfo
Date: 16-July-2002
Author: Ryan N Payet
Description: Return rows for the given table and
search criteria
Version History
---------------
0.1, 16-July-2002, Ryan N Payet:
Procedure Created
******************************************/
@TableName nvarchar(100),
@ColumnName nvarchar(100) = '1',
@Value sql_variant = '1',
@Operator nvarchar (15) = '='
AS
BEGIN
/*Declare variable to hold script*/
DECLARE @@SQLScript as nvarchar(4000)
DECLARE @@SQLPARAMETER as nvarchar(1000)
/*Declare variable to manage errors*/
DECLARE @@ERRORMSG as varchar(256)
DECLARE @Error_Code as integer
|
| Note |
SLQ_variant is a data type that can store values of any SQL Server data types with the exception of text, ntext, timestamp, image, and SQL_ variant. |
We must make sure that the table name and column name given are valid.
|
/*Check if table exist*/
if NOT exists
(SELECT * FROM dbo.sysobjects
WHERE id = object_id(@TableName)
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
SET @@ERRORMSG = 'Table "' + @TableName
+ '" does not exist in database'
RAISERROR ( @@ERRORMSG , 16, 1)
RETURN 1
END
/*If you base a number as column name then
do not check for existence of column.
This allows it to return all rows
by passing a column 1 and a value 1
*/
IF ISNUMERIC(@ColumnName) = 0
BEGIN
IF NOT exists
( SELECT * FROM syscolumns
WHERE (id = OBJECT_ID(@TableName))
AND UPPER([name]) = UPPER(@ColumnName))
BEGIN
SET @@ERRORMSG =''
+ 'Column "' + @ColumnName
+ '" does not exist in table "'
+ @TableName + '"'
RAISERROR (@@ERRORMSG , 16, 1)
RETURN 1
END /*IF NOT exists */
END /*IF ISNUMERIC(@ColumnName) = 0*/
|
We can then build our script. The point to note here is that @Value is not a variable but a string literal in the @@SQLScript variable. We also get the value of @@ERRORR right after the SELECT in the script. We build a parameter list to be used with sp_executesql in @@SQLPARAMETER and when it is passed in sp_executesql. @error_code is defined as an OUTPUT type. This is necessary so that we can get a value from the script passed in sp_excutesql. Also, the parameters must be used in the same order that they are defined in @@ SQLPARAMETER.
|
/*Build Script*/
SET @@SQLScript = ''
+ 'SELECT * FROM ' + @TableName + Char(13)
+ 'WHERE ' + @ColumnName
+ ' ' + @Operator +
+ ' @Value' + Char(13)
+ 'SET @Error_Code = @@ERROR'
/*Build parameter for script*/
SET @@SQLPARAMETER = '@Value sql_variant,'
+ ' @error_code integer OUTPUT'
/*This so that we know the script
executed properly and set error_code to
0 if there is no error*/
SET @error_code = 1
EXECUTE sp_executesql @@SQLScript,
@@SQLPARAMETER ,
@Value,
@error_code OUTPUT
IF @error_code <> 0
BEGIN
SET @@ERRORMSG ='Error in script'
RAISERROR (@@ERRORMSG , 16, 1)
RETURN 1
END
RETURN 0
END
GO
|
To test the stored procedure, try the
| Note |
CHAR(13) is ASCII character 13 (carriage return) and CHAR(39) is single quote character, useful when writing strings within a string. |
EXECUTE sp_GetRows 'Orders', 'EmployeeID', 6
With a lot of practice and creative programming, you can do a great deal more with dynamic scripts. One of the down sides of dynamic scripting, apart from the obvious complexity, is performance. Since the real SQL logic of the script is in string variables, you lose the benefit of executing plans and optimization by the database engine. This is not noticeable if the generated script is simple, but it can be an issue if the generated script is very complex.
|
|