AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=945&pId=-1
CodeSnip: How to Create a Complete Dynamic SQL Statement Using SQL Server
page
by Nidal Arabi
Feedback
Average Rating: 
Views (Total / Last 10 Days): 23527/ 25

Introduction

Microsoft SQL Server 2000 does not allow the use of variables in some parts of your SQL Statement.  Two examples of this non use are selecting a variable top number of rows and defining a variable where clause.  However, there are several ways to overcome these obstacles.  In this article I will define an SQL stored procedure called DynamicSQL.  This procedure allows the caller to provide multiple parameters.

Solution

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.

Examples

An example of calling the stored procedure is provided below.  It can select the first 5 rows from the table called Products and order them by the ProductName field after filtering for the product name that starts with the letter A from the Northwind database.  You can copy the code in Listing 2 directly to an SQL Server Query analyzer.

Listing 2

Use Northwind
Exec DynamicSql 5,
 'ProductName, SupplierID,QuantityPerUnit','Products','Order By ProductName','Where ProductName like ''A%'''

The following result is generated:

ProductName SupplierID QuantityPerUnit

Alice Mutton 7 20 - 1 kg tins

Aniseed Syrup 1 12 - 550 ml bottles

Another example with the usage of the output parameter is presented below:

Exec DynamicSql 1,
'@ProdName = ProductName','Products',null,'Where ProductName like ''A%''','@ProdName', 'varchar'

This example would return the string "Alice Mutton" to the calling part.

Risky Point to Avoid

In order to prevent or minimize the probability of an SQL injection happening, you can check for any semicolons in the string and replace them with a space.  An example replace function is presented below.

Listing 3 - The CleanChars Function used to prevent SQL Injection

CREATE FUNCTION dbo.CleanChars
(@Str varchar(8000)) Returns Varchar(8000)
Begin
While CHARINDEX(';', @Str) > 0 Set @Str = replace(@StrSubstring(@Str,CHARINDEX(';',@Str),2),' ')
return @Str
End

This function would clean the passed string of any semicolon and replace it with a space. Each string passed to DynamicSql stored procedure should be checked against the CleanChars function in the following pattern.

Listing 4

Set @FieldList = dbo.CleanChars(@FieldList)

After doing the check for every alphanumeric passed variable, you should be safe when calling the DynamicSql stored procedure.

Conclusion

In this article you have learned how to create a dynamic SQL Statements using SQL Server 2000 with the help of code samples.  Thank you for taking the time to read my article.



©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 3:20:35 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search