1) How many top rows the user would like to select (0 if all
rows are to be selected)
2) A field list (separated by commas) to be selected or
"*" to select all fields in the table(s)
3) A table name or a list of tables separated by a comma
4) An order by clause
5) A Where Clause
6) An output parameter if used (null if not provided)
7) An output parameter type (int, bigint, etc…)
A copy of the stored procedure is shown below.
Listing 1: The Complete Copy of The DynamicSQL
Stored Procedure
Use Northwind
Create Procedure DynamicSql (@TopRows int,
@FieldList varchar(1000),
@TableName varchar(100),
@OrderDirection varchar(500) = null,
@DynamicWhere varchar(1000) = null,
@OutParamater varchar(100) = null Output,
@OutType varchar(15)= null )
As
Begin
Declare @MyNewSql nvarchar(4000)
Declare @ParamList nvarchar(500)
Declare @ParamString nvarchar(500)
-- insert the top number of rows in the sql as well as the fields list and the table name
If @TopRows <= 0
Begin
Set @MyNewSql = 'Select '
End
Else
Begin
Set @MyNewSql = 'Select Top ' + Convert(varchar,@TopRows) + ' '
End
Set @MyNewSql = @MyNewSql + @FieldList + ' From ' + @TableName
-- Insert the dynamic where now
if not @DynamicWhere is null
Begin
Set @MyNewSql = @MyNewSql + ' ' + @DynamicWhere
End
-- INSERT THE DYNAMIC ORDER BY CLAUSE
if not @OrderDirection is null
Begin
Set @MyNewSql = @MyNewSql + ' ' + @OrderDirection
End
-- CHECK IF THERE IS AN OUTPUT PARAMETER
If Not @OutParamater is null
Begin
Set @ParamList = @OutParamater + ' ' + @OutType + ' Output'
Set @ParamString = @OutParamater + 'Output'
Exec SP_EXECUTESQL @MyNewSql, @ParamList, @ParamString
End
Else
Begin
Execute(@MyNewSql)
End
End
Explanation
I think that the procedure is self-explanatory, especially with
some comments inside it. Note that in order to use this procedure, it is
enough to copy and paste the procedure to any database (In my case, the
procedure was created in the Northwind example database that ships with
Microsoft SQL Server 2000).
Note that the execute statement provided at the end of the
procedure is the key to this procedure. The Execute statement is provided by
SQL Server to execute any dynamic SQL statement at run time. As for the
execution of a procedure with parameters, I am calling a stored procedure
called SP_EXECUTESQL that would create virtual stored procedures on the fly
with the supplied parameters and return the result to you.
However, there are several precautions that you should take
into consideration when using this procedure.
1) The SQL Server engine has no way of checking the syntax
of the SQL statement that you have provided until it is run. If you supply an
invalid table name or invalid field name, the SQL statement will not run
correctly. However, you will be notified of this and you will then have to
catch this error in your code.
2) The procedure provides a way for a hacker to invoke a
high risk SQL Injection statement that may affect your database.
3) The new SQL Statement is not compiled in the database.
In other words, you loose the speed of execution that SQL Server provides with
a compiled stored procedure.
