CodeSnip: How to Create a Complete Dynamic SQL Statement Using SQL Server
page 2 of 5
by Nidal Arabi
Feedback
Average Rating: 
Views (Total / Last 10 Days): 27841/ 83

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.


View Entire Article

User Comments

Title: hello   
Name: asa
Date: 2012-11-17 7:52:29 AM
Comment:
heelo
Title: q   
Name: q
Date: 2012-09-11 9:54:43 AM
Comment:
q
Title: Dynamic sql   
Name: mala
Date: 2009-10-23 8:23:59 AM
Comment:
very very nice article.....

this article help to me....thanks a lot!..






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-09-17 4:45:28 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search