sql dynamic چیست؟

[saed2006]

با سلام
sql dynamic چیست؟ چه کاربردی داره؟ منابع فارسی اموزشی رو دارین معرفی کنید لطفا
با تشکر

[saed2006]

یعنی توی این سایت با این همه کاربر کسی نیست بدونه sql dynamic چی هست؟

[Elham_gh]

يه مقاله دارم. يادم نيست از كجا آوردم.اما نويسنده اش معلومه. نه خودش يا zip شدش و نمي شه اينجا گذاشت. تو سايت ديگه هم نمي ذارم چون بعد چند وقت قابل دستيابي نيست. اينه كه هميجا در چند قسمت كپي اش مي كنم.

[Elham_gh]

Dynamic Search Conditions in T-SQL
An SQL text by Erland Sommarskog, SQL Server MVP.
An older version of this article is available in German, in translation by SQL Server MVP Frank Kalis.


Introduction
A very common requirement in an information system is to have a function (or several functions) where the users are able to search the data by selecting freely among many possible criterias. This is a tough challenge, because not only must you produce the desired output, but you must also keep the response time within acceptable limits, at least for common searches. And on top of all, the code must be maintainable, so you can meet new needs and requirements.
In this text I will look at various techniques to solve this problem. There are two main alternatives: dynamic SQL and static SQL. There are also hybrid solutions that use both. As soon as the number of possible search conditions is more than just a handful, dynamic SQL is the most effective solution, in terms of performance, development and maintenance. On SQL 2000 and earlier, to use dynamic SQL in its pure form. you need to give the users direct SELECT permissions to the involved tables, and this is far from always permissible. SQL 2005 permit ways around this issue.
I will first look at using dynamic SQL, and try to point out some traps that you should avoid. I will then cover techniques for static SQL to give you a set of methods and tricks that you can combine to implement your search functions. Finally, I will present two hybrid solutions that use both dynamic and static SQL and in this way evades the permissions problem.
This text applies to all versions of SQL Server from SQL 7 and on.

Here is a table of contents:
Introductio
n

The Case Study: Searching Orders
The Northgale Database
Dynamic SQL
Introducti
on

Using sp_executesql
Using the CLR
Using EXEC()
When Caching Is Not Really What You Want
Static SQL
Introduction[IND
ENT]
x = @x OR @x IS NULL
Using IF statements
Umachandar's Bag of Tricks
Using Temp Tables
x = @x AND @x IS NOT NULL
Handling Complex Conditions[/INDENT]
Hybrid Solutions – Using both Static and Dynamic SQL

Using Views
Using Inline Table Functions
Conclusion
Feedback and Acknowledgements
Revision History


The Case Study: Searching Orders
Throughout this text, we will work with implementing a stored procedure that retrieves information about orders in the Northwind and Northgale databases, as well as the customers and the products of the orders. Northwind is a sample database that comes with SQL 7 and SQL 2000. Northwind does not ship with SQL 2005, but you can retrieve it from Microsoft's web site. Northgale is a bigger database that I have derived from Northwind, more about it below.
This is the interface that we expose to the user (or more probably to a GUI or middle-layer programmer):


CREATE PROCEDURE search_orders
@orderid int = NULL,
@fromdate datetime = NULL,
@todate datetime = NULL,
@minprice money = NULL,
@maxprice money = NULL,
@custid nchar(5) = NULL,
@custname nvarchar(40) = NULL,
@city nvarchar(15) = NULL,
@region nvarchar(15) = NULL,
@country nvarchar(15) = NULL,
@prodid int = NULL,
@prodname nvarchar(40) = NULL AS





SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
WHERE ???
ORDER BY o.OrderID



You see in the SELECT list what information the user gets. Here is a specification of the parameters:
http://barnamenevis.org/forum/attachment.php?attachmentid=30382&stc=1&d=1241002521
If customer leaves out a search condition, that search condition should not apply to the search. Thus a plain EXEC search_orders should return all orders in the database.
This example is fairly simple, since each condition can be implemented with a single condition using =, <=, >= or LIKE. In many real-life situations you have more conditions that affects which tables or columns you need to access. I have purposely left out such conditions from search_orders, in order to keep focus on the main issues and I only touch more complex conditions in a few places. My hope is that when you have read this article, you will have a better ground to stand on so that you will be able to tackle the more complex conditions you may run into on your own.
Sometimes you may want to search for a list of values, like @prodid = '14,56,76'. The way to deal with this is to unpack the list with table-valued function. This is not something I cover here, as I have an article Arrays and Lists in SQL Server on my web site, that is devoted to this topic.
It is not uncommon that there is a requirement for the user to be able to specify how the output is to be sorted. Since this article is not long enough, I will not dwell on this topic more than this brief note. If you are using dynamic SQL, you would of course build the ORDER BY clause dynamically as well. If you are using static SQL, see this section in my general article on dynamic SQL for suggestions.
In the text I discuss a number different implementations of search_orders, unimaginatively named search_orders_1 etc. Some of them are included in whole in this text, others only in parts. All are available in the dynsearch directory on my web site. (The numbering of the procedure is somewhat out of order with the text. Originally there were seven of them, but as the article have evolved by time I've added more, but I have not renumbered existing procedures.)
To know what I'm talking about when it comes to performance, I have benchmarked all my search procedures for six different search conditions. While I'm referring to my results in this article, I am not disclosing any numbers, because they are not generally applicable. How good or bad a solution is in a certain situation depends on available indexes, data distribution etc. Throughout this article I will stress that if you care about performance, it is necessary that you run your own benchmarks with production-size data. This applies not the least if you are constrained to static SQL only..

[Elham_gh]

The Northgale Database
The Northwind database is very small, so it is very difficult to draw conclusions about the performance from queries in this database. A query that one would expect to use an index, may in fact use a scan, simply because at those small volumes, the scan is cheaper.
For this reason I have composed the Northgale database. Northgale has the same tables and indexes as Northwind, but I have exploded the data so that instead of 830 orders, there are 344035 of them. To install Northgale, run Northgale.sql. The data in Northgale is taken from Northwind, so this database must be in place. To install Northgale, you need 4.6 GB of disk space on SQL 2005 and 2.6 GB on SQL 2000. Once installed, the database takes up 520 MB. (Log space needed when inserting the data is reclaimed at the end of the script.) By default, the database is installed in the same directory as the master database, but you can edit the script to change that.
I've exploded the data is by cross-joining the tables. For IDs, I've composed combinations, so there is for instance now 6640 customers rather than 91 as in the original Northwind. I have also generated new customer and product names by permuting the parts of the names. However, I have not created new cities, countries or regions, so a search on a city alone can yield very many hits.
Keep in mind that Northgale too is a small database by today's standards. For instance, it easily fits into cache entirely on reasonably equipped server. A poorly written query that requires a scan of, say, the Orders table, still returns within a few seconds. It's hopefully big enough to give a sense for how good or bad different solutions are, but I would advise you to not draw far-reaching conclusions. It is also worth pointing out that the way the database was composed, the distribution of data is a bit skewed
When you implement a dynamic search function, you should always benchmark your solution with your production database, as well inspect query plans for common search cases.
----------------------------------------------------------------------------------------------------
Dynamic SQL
Introduction
Performance
You might have been told: Don't use dynamic SQL, it's slow!. Yes, used wrongly and in the wrong place dynamic SQL can degrade performance, and sometimes severely. But if you use sp_executesql and you always refer to your tables in two-part notation, the penalty for dynamic SQL is about negligible. On the other hand, if you use EXEC() and refer to tables with only the table name there can be a cost – not for running the query itself. The cost is for building the query plan. With stored procedures and correctly written dynamic SQL, SQL Server can find a query plan in cache that it can reuse.
For a dynamic search like our search_orders, there is no single plan that fits all combinations of input parameters. One plan which may give sub-second response when the user specifies the product, may take two minutes if the user specifies the customer or vice versa. In this case, some extra 100 ms to build a query plan for each new combination of search parameters is a price well worth paying. Particularly, if the plan stays in cache and can be reused by other users.
Thus, from a performance perspective, dynamic SQL is often a superior solution for this sort of problem.

Security
On SQL 2000, there is an important security aspect you must be aware of. Recall that with stored procedures, users do not need to have direct access rights to the tables and views the procedures refer to; they only need permission to execute the procedures. When a stored procedure is running, the rights of the procedure owner apply. However, dynamic SQL created in a stored procedure is not part of that procedure, and when the dynamic SQL code executes, it is the permissions of the current user that count. Thus, to use dynamic SQL you need to grant your users SELECT access directly to the tables. If this is not acceptable in your system, you cannot use dynamic SQL.
...well, it is not 100% true. There are hybrid solutions where you put the static SQL in a view or an inline-table function. Particularly the latter can give you almost all the power of dynamic SQL, without having to expose tables directly to the users. We will look into this in the section Hybrid Solutions – Using Both Static and Dynamic SQL.
On SQL 2005, there are two more ways to avoid the permissions issue: You can sign the procedure with a certificate and then associate the certificate with a virtual user that you grant the necessary permissions. You can also use the EXECUTE AS clause to impersonate such a virtual user. The latter solution is less hassle to implement, but it has side effects that can break row-level security schemes and make system monitoring more difficult. I describe both these methods in detail in my article Granting Permissions through Stored Procedures.

Further Reading
There are more things to say about dynamic SQL as such, that I will not go into here. Rather I refer you to my web article The Curse and Blessings of Dynamic SQL, which discusses the use of dynamic SQL in general. In this article, I also cover the performance and security topics in more detail.

Testing is Necessary!
Because of the way you generate the code, it is very important that you test all input parameters, and preferably some combinations of them too. If you are not careful, the users can experience syntax errors from your code when you build queries dynamically.
You should also test that you get the desired performance, using data volumes in parity to what you can expect in production.

The Methods to Do Dynamic SQL
There are three ways to go:

A T-SQL procedure that builds a query string and executes it with sp_executesql. 1

2.A CLR procedure that builds a parameterised query string and executes it. (SQL 2005 only.)

3.A T-SQL procedure that builds a query string and executes it with EXEC().

Of these three, the first two are both good choices, and use what you find the most convenient. Using EXEC() is a inferior method that is more difficult to work with and your prospects for query-plan reuse are slim. There are however a few special situations where EXEC() and the techniques around it comes into play.
I will in detail discuss an implementation of search_orders that uses sp_executesql, and I will also present two CLR implementations. I also show an implementation of search_orders that uses EXEC(), but this can be considered extra reading that you can skip if you like. Before I move on to static SQL, I will look a little at situations where cached query plans hampers your performance. This, too, you can regard as extra reading and skim over at first reading.

[Elham_gh]

Using sp_executesql
sp_executesql is a system procedure that takes an SQL statement as its first parameter, and a declaration of parameters as the second parameter, and the remaining parameters are determined by that parameter list. Here is the procedure search_orders_1, which uses sp_executesql:



CREATE PROCEDURE search_orders_1 -- 1
@orderid int = NULL, -- 2
@fromdate datetime = NULL, -- 3
@todate datetime = NULL, -- 4
@minprice money = NULL, -- 5
@maxprice money = NULL, -- 6
@custid nchar(5) = NULL, -- 7
@custname nvarchar(40) = NULL, -- 8
@city nvarchar(15) = NULL, -- 9
@region nvarchar(15) = NULL, -- 10
@country nvarchar(15) = NULL, -- 11
@prodid int = NULL, -- 12
@prodname nvarchar(40) = NULL, -- 13
@debug bit = 0 AS -- 14
-- 15
DECLARE @sql nvarchar(4000), -- 16
@paramlist nvarchar(4000) -- 17
-- 18
SELECT @sql = -- 19
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, -- 20
c.CustomerID, c.CompanyName, c.Address, c.City, -- 21
c.Region, c.PostalCode, c.Country, c.Phone, -- 22
p.ProductID, p.ProductName, p.UnitsInStock, -- 23
p.UnitsOnOrder -- 24
FROM dbo.Orders o -- 25
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID -- 26
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID -- 27
JOIN dbo.Products p ON p.ProductID = od.ProductID -- 28
WHERE 1 = 1' -- 29
-- 30
IF @orderid IS NOT NULL -- 31
SELECT @sql = @sql + ' AND o.OrderID = @xorderid' + -- 32
' AND od.OrderID = @xorderid' -- 33
-- 34
IF @fromdate IS NOT NULL -- 35
SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate' -- 36
-- 37
IF @todate IS NOT NULL -- 38
SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate' -- 39
-- 40
IF @minprice IS NOT NULL -- 41
SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice' -- 42
-- 43
IF @maxprice IS NOT NULL -- 44
SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice' -- 45
-- 46
IF @custid IS NOT NULL -- 47
SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' + -- 48
' AND c.CustomerID = @xcustid' -- 49
-- 50
IF @custname IS NOT NULL -- 51
SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%''' -- 52
-- 53
IF @city IS NOT NULL -- 54
SELECT @sql = @sql + ' AND c.City = @xcity' -- 55
-- 56
IF @region IS NOT NULL -- 57
SELECT @sql = @sql + ' AND c.Region = @xregion' -- 58
-- 59
IF @country IS NOT NULL -- 60
SELECT @sql = @sql + ' AND c.Country = @xcountry' -- 61
-- 62
IF @prodid IS NOT NULL -- 63
SELECT @sql = @sql + ' AND od.ProductID = @xprodid' + -- 64
' AND p.ProductID = @xprodid' -- 65
-- 66
IF @prodname IS NOT NULL -- 67
SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%''' -- 68
-- 69
SELECT @sql = @sql + ' ORDER BY o.OrderID' -- 70
-- 71
IF @debug = 1 -- 72
PRINT @sql -- 73
-- 74
SELECT @paramlist = '@xorderid int, -- 75
@xfromdate datetime, -- 76
@xtodate datetime, -- 77
@xminprice money, -- 78
@xmaxprice money, -- 79
@xcustid nchar(5), -- 80
@xcustname nvarchar(40), -- 81
@xcity nvarchar(15), -- 82
@xregion nvarchar(15), -- 83
@xcountry nvarchar(15), -- 84
@xprodid int, -- 85
@xprodname nvarchar(40)' -- 86
-- 87
EXEC sp_executesql @sql, @paramlist, -- 88
@orderid, @fromdate, @todate, @minprice, -- 89
@maxprice, @custid, @custname, @city, @region, -- 90
@country, @prodid, @prodname -- 91



In case you are reading this with a narrow browser window, I should point out that there are line numbers to the right that I will refer to in the following text.

Overall Flow
On lines 19-29, I compose the basic SQL string. The condition WHERE 1 = 1 on line 29 is there to permit the users to call the procedure without specifying any parameters at all.
Then on lines 31-68, I check all parameters (save @debug), and if a parameter is non-NULL, I add a condition for the corresponding column to the SQL string. Finally on line 70, I add the ORDER BY clause.
On line 72, I test the @debug parameter. If 1, I print the SQL string. This is handy, if the dynamic SQL yields a compilation error that I don't understand. Once I see the SQL code the error might be apparent. A typical error is to miss a space, leading to code that reads:
WHERE 1 = 1 AND o.OrderDate <= @xtodateAND p.ProductName LIKE @xprodname
On lines 75-86 I declare the parameter list for my dynamic SQL statement, and on lines 88-91 I finally execute it.

A Little More in Detail on sp_executesql
sp_executesql is a system procedure with a very special parameter list. The first parameter is a parameterised SQL statement. The second parameter is a parameter-list declaration, very similar to the parameter list to a stored procedure. And the remaining parameters are simply the parameters defined by that parameter-list parameter.
The parameterised query is placed in cache, so if a second user make a search using the same input parameters (for instance @city and @prodname), the query plan will be reused, even if he uses different values in his search.
The parameter-list parameter that I pass to sp_executesql is basically the same as the parameter list to the procedure itself. Here, I have called the parameters @xorderid and so on. There is not any technical reason for this, and normally I would have re-used the names in the parameter list of the stored procedure. But I wanted to make you aware of that the parameters inside the dynamic SQL have no relation to the parameters and variables in the surrounding stored procedure. The dynamic SQL constitutes a scope on its own. Think of it as a stored procedure that you create on the fly.
The SQL statement and the parameter must be Unicode strings, so the @sql and @paramlist variables must be nvarchar. And if you pass the SQL statement or the parameter-list as literals, you must use N (for National) denote a Unicode literal:
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID = @orderid',
N'@orderid int', @orderid = 10872
You can use OUTPUT parameters too with sp_executesql. I'm not showing any example of this here, but see the section on sp_executesql in Curse and Blessings...

Declaring the SQL String
You can see that on line 16, I have this declaration:


Declaring the SQL String
You can see that on line 16, I have this declaration:

In SQL 7 and SQL 2000, there is no way to work with variables with more than 8000 bytes, which imposes a practical limit when working with sp_executesql, although the statement parameter to sp_executesql is ntext on these versions of SQL Server. It's still possible to work with longer query strings if you use EXEC(). (You can even wrap sp_executesql in EXEC(). There is an example in Curse and Blessings... of this.)
SQL 2005 introduced a new data type, nvarchar(MAX) which is unlimited just like ntext, but without all the restrictions. You can declare your @sql variable to be nvarchar(MAX) and leave worries behind about outgrowing the variable. (I did not use nvarchar(MAX) in search_orders_1 so that it would run on SQL 7 and SQL 2000 as well.)

The dbo Prefix
On lines 25-28 there is something very important:


FROM dbo.Orders o
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
JOIN dbo.Products p ON p.ProductID = od.ProductID

As you can see, I refer to all tables in two-part notation. That is, I also specify the schema (which in SQL 7/2000 parlance normally is referred to as owner.) If I would leave out the schema, each user would get his own his own private version of the query plan, so if there are ten users searching for orders there will be ten instances of the plan in cache. (As discussed in Curse and Blessings... it may work without specifying the schema on SQL 2005, but you should not rely on that.)
(The author must shamefully confess that earlier versions of this article did not prefix the tables with dbo, and yet I said that query plans would be reused by other users.)

Double Feature
You may wonder why there are two conditions on the order id, customer id and product id (lines 32-33, 48-49 and 64-65). This is because I once learnt that you should always specify all conditions you know of to help the optimizer out. That was very long ago, and the rule may no longer apply. But I included it here nevertheless.

Picking Different Tables
Assume for the example, that there had been a table Historicorders in Northwind for orders that are shipped, invoiced and paid for, and users should be given the choice to search either current or historic orders. (For simplicity, I'm overlooking Order Details here.) How would you solve this?
You may be tempted to try something like SELECT ... FROM @ordertable and then add @ordertable as a parameter. However, T-SQL does not permit you to parameterise the table name. (And for good reasons as I discuss in Curse and Blessings...)
In fact, you should not pass the table name as a parameter to the procedure at all; once you have started to use stored procedures, all references to table and column names should be in the procedures themselves. The correct way would be add another parameter to search_orders:


historic_data bit DEFAULT = 0
and then line 25 would read:
FROM dbo.' + CASE @historic_data
WHEN 0 THEN 'Orders'
WHEN 1 THEN 'Historicorders'
END + ' o



Trying it out
Here are some test cases:


EXEC search_orders_1 @orderid = 11000
EXEC search_orders_1 @custid = 'ALFKI'
EXEC search_orders_1 @prodid = 76
EXEC search_orders_1 @prodid = 76, @custid = 'RATTC'
EXEC search_orders_1 @fromdate = '19980205', @todate = '19980209'
EXEC search_orders_1 @city = 'Bräcke', @prodid = 76

If you try these and inspect the query plans, you will see that in the available indexes on the search columns is used in Northgale with one exception: the index on Customers.City is not used for the last case, but this column is not very selective. If you run the queries in Northwind, you will see more scans, but this is due to the small size of that database.

[Elham_gh]

Using the CLR
SQL 2005 adds the possibility to write stored procedures in languages that use the CLR (Common Language Runtime), such as Visual Basic .Net or C#. A dynamic search can be implemented in the CLR just as well as in T-SQL. After all, search_orders_1 is all about string manipulation until it invokes sp_executesql.
If you are more comfortable working in VB or C#, you could just as well implement your searches in the CLR rather than T-SQL. The reverse also applies: if you feel more comfortable with T-SQL, there is little reason to use the CLR to implement dynamic searches.
I've written two CLR procedures, search_orders_vb and search_orders_cs, that I will discuss in this section. As the code is fairly repetitive, I'm not including any of them in full here, but I only highlight some important points. Beware, that I will not go into any details on writing CLR stored procedures as such. If you have never worked with the CLR before, but are curious, I refer you to Books Online. At the end of this section there are instructions on how to create these two procedures in SQL Server.

Setting up the Statement
This is how search_orders_cs starts off:


string Query;
SqlCommand Command = new SqlCommand();

Query = @"SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City,
c.Region, c.PostalCode, c.Country, c.Phone,
p.ProductID, p.ProductName, p.UnitsInStock,
p.UnitsOnOrder
FROM dbo.Orders o
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
JOIN dbo.Products p ON p.ProductID = od.ProductID
WHERE 1 = 1 ";


As you can see this is very similar to search_orders_1, including the dbo prefix. The rule that you should use two-part notation to maximise query-plan reuse applies to CLR procedures as well.

Defining the Parameters
This is the very important part. Far too often on the newsgroups, I see posters who interpolate the parameter values into the query string. This is bad for several reasons. One is that you get very little chance for query-plan reuse. The second and even more important reason is a security risk known as SQL injection, which I discuss in Curses and Blessings...
What you should do is to build parameterised statements. Here is how the @custid parameter is added in search_orders_cs:
if (! Custid.IsNull) {
Query += " AND o.CustomerID = @custid" +
" AND c.CustomerID = @custid";
Command.Parameters.Add("@custid", SqlDbType.NChar, 5);
Command.Parameters["@custid"].Value = Custid;
Command.Parameters["@custid"].Direction = ParameterDirection.Input;
}
As in the T-SQL example, the query string is extended with the conditions for the parameter in both Orders and Customers.
What is different from T-SQL is how we define the parameter list and supply the value. In T-SQL the parameter list is a string, which includes all possible parameters. When working with the CLR, we only define the parameters that actually are in use. We define a parameter by adding it to the Parameters collection of the Command object. There are a number of ways to do this, and I refer you MSDN Library for a complete reference. The example shows a pattern that works for the most commonly used data types. The first parameter is the variable name, while the second parameter is the type indicator from the SqlDbType enumeration. The last parameter is the length, which you need to specify for the char, varchar, nchar, nvarchar, binary and varbinary data types, but you would leave it out for fixed-length types. Note that for decimal/numeric parameters, you need to use some different way to add them, as this flavour does not have means to specify scale and precision.
Once the parameter is defined, I assign the value separately. I also explicitly specify the direction, although this is hardly necessary.
Here is the above in Visual Basic .Net:


If Not Custid.IsNull Then
Query &= " AND o.CustomerID = @custid" & _
" AND c.CustomerID = @custid" & VbCrLf
Command.Parameters.Add("@custid", SqlDbType.NChar, 5)
Command.Parameters("@custid").Value = Custid
Command.Parameters("@custid").Direction = ParameterDirection.Input
End If

It's very similar to the C# example. Different operator for string concatenation, parentheses to address elements in the collection and no semicolons.

Running the Query
This how this looks like in C#:


using (SqlConnection Connection =
new SqlConnection("context connection=true"))
{
Connection.Open();

if (Debug) {
SqlContext.Pipe.Send(Query);
}

Command.CommandType = CommandType.Text;
Command.CommandText = Query;
Command.Connection = Connection;
SqlContext.Pipe.ExecuteAndSend(Command);
}

Which very much is standard how you run a query from a CLR procedure. SqlContext.Pipe.Send is how you say PRINT in the CLR.

Loading the Examples
If you have any flavour of Visual Studio 2005 (including the Express editions), you can deploy search_orders_cs and search_orders_vb from Visual Studio. (But please don't ask me how to do it, Visual Studio just leaves me in a maze.)
Since the .Net Framework comes with SQL Server and includes compilers for the most common .Net languages, you can also load them without Visual Studio. First make sure that C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 (or corresponding) is in your path. Then run from a command-line window:


csc /target:library search_orders_cs.cs
vbc /target:library search_orders_vb.vb

To load the DLLs into SQL Server, you can use load_clr_sp.sql. You will have to change path in the CREATE ASSEMBLY command to where you placed the DLLs. Note that paths are as seen from SQL Server, so if you don't have SQL Server on your local machine, you will have to copy the DLLs to the SQL Server box, or specify a UNC path to your machine.

Trying it out
The same test cases as for search_orders_1:


EXEC search_orders_cs @orderid = 11000
EXEC search_orders_cs @custid = 'ALFKI'
EXEC search_orders_cs @prodid = 76
EXEC search_orders_cs @prodid = 76, @custid = 'RATTC'
EXEC search_orders_cs @fromdate = '19980205', @todate = '19980209'
EXEC search_orders_cs @city = 'Bräcke', @prodid = 76

The query plans should be identical to search_orders_1, as it is the same queries. Hint: if you want to look at the query plans, you need use Profiler, and get the Showplan XML event. For some reason, you don't see query plans for queries submitted from CLR code in Management Studio or Query Analyzer.

[Elham_gh]

Using EXEC()
As I've already mentioned, EXEC() is an inferior solution to sp_executesql and the CLR. The reason for this is that since EXEC() does not take parameters, you have to build a query string with parameter values interpolated. This precludes query plans from being reused. It also makes the programming more difficult.
Nevertheless, there are three situations where you need to use EXEC() or at least interpolate parameter values:
*You are on SQL 7 or SQL 2000, and the query string could exceed 4000 characters. As you can say


EXEC(@sql1 + @sql2 + @sql3 ...)

there is no practical limit to the length of the query string with EXEC(). On SQL 2005 you can use nvarchar(MAX), with sp_executesql, so this issue does not exist there.
*You use a hybrid solution with a inline table function, a solution I will discuss in detail later in this article.
*You opt to interpolate the value of a certain parameter into the query. We will look into why you want to this in the section When Caching Is Not Really What You Want.
So while it's not equally essential to master EXEC() as sp_executesql to build dynamic search functions, it can still be worthwhile to study the technique so you can avoid the pitfalls if you would need to use it. But feel free to skip this section in your first reading if you are in a hurry and come back when you actually need to use it.

search_orders_2
In difference to sp_executesql, EXEC() only takes a string as a parameter, so you can not parameterise the query. Instead you have to put the values into the SQL string. At first glance you may find this simpler, but as we shall see, this is in fact more complicated. Here is the procedure search_orders_2:


CREATE PROCEDURE search_orders_2 -- 1
@orderid int = NULL, -- 2
@fromdate datetime = NULL, -- 3
@todate datetime = NULL, -- 4
@minprice money = NULL, -- 5
@maxprice money = NULL, -- 6
@custid nchar(5) = NULL, -- 7
@custname nvarchar(40) = NULL, -- 8
@city nvarchar(15) = NULL, -- 9
@region nvarchar(15) = NULL, -- 10
@country nvarchar(15) = NULL, -- 11
@prodid int = NULL, -- 12
@prodname nvarchar(40) = NULL, -- 13
@debug bit = 0 AS -- 14
-- 15
DECLARE @sql1 nvarchar(4000), -- 16
@sql2 nvarchar(4000), -- 17
@fromdatestr char(23), -- 18
@todatestr char(23), -- 19
@minpricestr varchar(25), -- 20
@maxpricestr varchar(25) -- 21
-- 22
SELECT @fromdatestr = convert(char(23), @fromdate, 126), -- 23
@todatestr = convert(char(23), @todate, 126), -- 24
@minpricestr = convert(varchar(25), @minprice), -- 25
@maxpricestr = convert(varchar(25), @maxprice) -- 26
-- 27
SELECT @sql1 = -- 28
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, -- 29
c.CustomerID, c.CompanyName, c.Address, c.City, -- 30
c.Region, c.PostalCode, c.Country, c.Phone, -- 31
p.ProductID, p.ProductName, p.UnitsInStock, -- 32
p.UnitsOnOrder -- 33
FROM dbo.Orders o -- 34
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID -- 35
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID -- 36
JOIN dbo.Products p ON p.ProductID = od.ProductID -- 37
WHERE 1 = 1' -- 38
-- 39
IF @orderid IS NOT NULL -- 40
SELECT @sql1 = @sql1 + ' AND o.OrderID = ' + str(@orderid) + -- 41
' AND od.OrderID = ' + str(@orderid) -- 42
-- 43
IF @fromdate IS NOT NULL -- 44
SELECT @sql1 = @sql1 + ' AND o.OrderDate >= ' + -- 45
quotename(@fromdatestr, '''') -- 46
-- 47
IF @todate IS NOT NULL -- 48
SELECT @sql1 = @sql1 + ' AND o.OrderDate <= ' + -- 49
quotename(@todatestr, '''') -- 50
-- 51
IF @minprice IS NOT NULL -- 52
SELECT @sql1 = @sql1 + ' AND od.UnitPrice >= ' + @minpricestr -- 53
-- 54
IF @maxprice IS NOT NULL -- 55
SELECT @sql1 = @sql1 + ' AND od.UnitPrice <= ' + @maxpricestr -- 56
-- 57
SELECT @sql2 = '' -- 58
-- 59
IF @custid IS NOT NULL -- 60
SELECT @sql2 = @sql2 + ' AND o.CustomerID = ' + -- 61
quotename(@custid, '''') + -- 62
' AND c.CustomerID = ' + -- 63
quotename(@custid, '''') -- 64
-- 65
IF @custname IS NOT NULL -- 66
SELECT @sql2 = @sql2 + ' AND c.CompanyName LIKE ' + -- 67
quotename(@custname + '%', '''') -- 68
-- 69
IF @city IS NOT NULL -- 70
SELECT @sql2 = @sql2 + ' AND c.City = ' + -- 71
quotename(@city, '''') -- 72
-- 73
IF @region IS NOT NULL -- 74
SELECT @sql2 = @sql2 + ' AND c.Region = ' + -- 75
quotename(@region, '''') -- 76
-- 77
IF @country IS NOT NULL -- 78
SELECT @sql2 = @sql2 + ' AND c.Country = ' + -- 79
quotename(@country, '''') -- 80
-- 81
IF @prodid IS NOT NULL -- 82
SELECT @sql2 = @sql2 + ' AND od.ProductID = ' + str(@prodid) + -- 83
' AND p.ProductID = ' + str(@prodid) -- 84
-- 85
IF @prodname IS NOT NULL -- 86
SELECT @sql2 = @sql2 + ' AND p.ProductName LIKE ' + -- 87
quotename(@prodname + '%', '''') -- 88
-- 89
IF @debug = 1 -- 90
PRINT @sql1 + @sql2 -- 91
-- 92
EXEC(@sql1 + @sql2 + ' ORDER BY o.OrderID') -- 93


General Notes
When building an non-parameterised query with the values included, you need to be disciplined when you write your code. It's very easy to get lost in a maze of nested quotes. I often see people on the newsgroups posting code like:


EXEC('SELECT col1, col2, ...
FROM ...
WHERE ' + CASE @par1 IS NULL THEN ' + col = ''' + @par + ''' + ...)

This is difficult to read and maintain, and if it goes wrong, you have no idea what SQL you are actually generating. search_orders2 aggregates the SQL code in two variables, and there is a @debug parameter so I can see the SQL code, if I need to verify that I am generating the right thing. And by using the function quotename(), I have tried to reduce the need for nested quotes to a minimum. (More about this function just below.)
Keep in mind that EXEC() opens a new scope. The SQL you generate can not refer to variables declared in the surrounding procedure, and if the dynamic SQL creates a temp table, that table goes away with the scope.

The Use of Quotename()
As you can see, starting from line 46, I make frequent use of the built-in function quotename(), an idea I got from SQL Server MVP Steve Kass. quotename() takes two parameters: a string and a delimiter character. If the delimiter is (, [, < or {, the corresponding bracket is assumed to be the closing delimiter. The return value is the string enclosed by the delimiters, and any closing delimiter in the string is doubled. The default delimiter is square brackets. Examples:


SELECT quotename('Order Details') -- => [Order Details]
SELECT quotename('Two o' + char(39) + 'clock', '''') -- => 'Two o''clock'

Of these examples, the interesting one in this context is the second. In T-SQL you use single quotes to delimit strings. If you need to include the string delimiter in a literal, you double it, so those four single quotes in a row is a one-character literal with the value of a single quote ('). Alternatively, you can express this value as char(39) like I do when I form the string Two o'clock in the example above.
Now, why all this? There are three important reasons:
1. It makes the code much easier to write. If you don't use quotename(), you need to use nested quotes, and the code becomes really messy.
2. This permits the user to enter values such as Let's Stop N Shop for the company name (this is an actual customer in the Northwind database) without getting a syntax error.
3. This increases you protection you against something which is known as SQL injection, a technique whereby a malicious intruder enters the string delimiter and then types in an SQL command where you as a naïve developer only expected data, and thereby manages to get SQL Server to execute that statement. See Curse and Blessings... for a longer discussion on SQL injection.
Note: The input parameter to quotename() is nvarchar(128), so if you have longer input parameters, you cannot use quotename(). You still need to be able to handle single quotes in the string. You can easily do this with the replace() function or use the quotestring() function from Curse and Blessings...

Datetime Parameters
All non-string parameters must be converted to string literals and on lines 23-24 I take care of the datetime parameters. The expression I use:
convert(char(23), @fromdate, 126)
is not something I picked at whim. The resulting format is like this: 2003-04-06T21:14:26.627, which is the precise format that is mandated by the standard ISO 8601, and which is commonly used in XML. More importantly, it is one of the three formats for datetime literals in SQL Server of which the interpretation does not depend on the settings for date format and language. If you don't understand what I'm talking about, try these statements:


SET DATEFORMAT mdy
SELECT convert(datetime, '02/07/09')
SET DATEFORMAT dmy
SELECT convert(datetime, '02/07/09')
go
SELECT convert(datetime, '2002-12-19') -- Fails!
go
SET LANGUAGE Swedish
SELECT convert(datetime, 'Oct 12 2003') -- Fails! (It's "Okt" in Swedish.)


Beside the SET commands, the language setting (which also controls the date format) can be set on user-level with the system procedure sp_defaultlanguage.
The style parameter 126, as well the format with the T between date and time, is available only on SQL 2000 and later. On SQL 7 you should instead use 112 for the style parameter, which gives the format 20030406, a second date format that is independent of date-format and language settings. The drawback with 112 is you do not get hours and minutes. For the procedure search_orders_2 this would be OK, since users are only giving dates, but if you need to include the time portion on SQL 7, you need to combine style parameters:


SELECT convert(char(8), getdate(), 112) + ' ' +
convert(char(8), getdate(), 108)

For further information about the various style parameters you can give to convert(), look under CAST and CONVERT in the T-SQL Reference of Books Online.

Numeric Parameters
On lines 25-26 I take care of the two money parameters, and on lines 41-42 and 83-84, I handle the int parameters @orderid and @prodid. As you see, I apply different strategies. Of course, I could have used convert() for @orderid and @prodid too. It is just that I find str() a little more convenient to use for integer values. str() works with money too, but with no further parameters, the value is rounded to the nearest whole number, whereas convert() by default retains two decimals, which is why I preferred convert() for the @min/maxprice parameters.
I'm not discussing the float and decimal data types here. Refer to the topics CAST and CONVERT and STR in Books Online.

@sql1 and @sql2
As you see, to build the string I use two variables, @sql1 and @sql2. In this example there is not really any reason for this, but if you have a huge query and large amount of parameters there is a risk on SQL 2000 and SQL 7 that your SQL string will exceed the maximum of 4000 characters for an nvarchar value (or even 8000 for varchar.) So to demonstrate the technique, there are two variables in search_orders_2. As you see, EXEC() permits you to pass an expression as a parameter. Note this is not an arbitrary expression; the only permitted operator is string concatenation.
On SQL 2005 you should use nvarchar(MAX), and you would have no reason to use more than one @sql variable.

varchar and nvarchar
The Northwind database consistently uses the nvarchar data type, but I've failed to handle this in my procedure. The data in Northwind appears to be restricted to the characters in Windows Latin-1, which covers languages such as English, Spanish, French and German, so if you have a system collation based on this character set, you would never get any incorrect response from search_orders_2. However, a Russian user trying:


EXEC search_orders_2 @city = N'Bräcke'

would not get the orders from Folk och Fä HB in Bräcke, because Bräcke would be converted to varchar as Bracke. This is how my procedure should have read: (lines 71-72)


SELECT @sql2 = @sql2 + ' AND c.City = N' +
quotename(@city, '''')

That is, I should have added an N to make the resulting SQL read c.City = N'Bräcke'. That N makes the literal a Unicode literal of the nvarchar data type, and it should appear with all nvarchar parameters. (The N stands for National, and is taken from the ANSI standard SQL-92.)
So why did I not give you the proper code? Well, I figured that many readers would take my code as a template for their own code, and I don't want you to include that N by routine. Because, if you use a nvarchar literal together with an indexed varchar column, the varchar column will be converted to nvarchar, and that precludes use of the index. (Because the index is sorted on an 8-bit value, not a 16-bit value.) Thus, you would get the right result, but performance would be poor.

Caching
As I've already mentioned, you get poor use of the cache with EXEC(), since the exact query string is placed in cache. This means that a search on customers ALFKI and VINET generates two different plans in the cache, so reuse only happens when two users performs exactly the same search.
Two qualifications here:
1. Sometimes SQL Server does not cache unparameterised queries at all, so there will be no reuse, even when two users search for the same thing.
2. SQL Server can auto-parameterise queries, but this happens only with the simplest queries, and is nothing you should rely on. On SQL 2005, there is something called forced parameterisation, where all queries are auto-parameterised, but again, you should not rely on this setting being active.

Summary
Just to make the point once more, here is a summary of the advantages of using sp_executesql over EXEC():
• Minimises the need to use nested quotes.
• Minimises risk for SQL injection.
• No need to worry about the formatting of datetime and numeric parameters.
• No need to think about whether use N before string literals or not. (But of course in the parameter list to sp_executesql you need to make the correct choice of data type.)
• Query plans for the same set of input parameters can be reused from cache.

[Elham_gh]

When Caching Is Not Really What You Want
Before we move on to static SQL, I like to discuss a problematic situation that you may encounter. This far I have preached the virtue of caching. If two users search on the same criterias, they get the same plan with search procedures like search_orders_1 or search_orders_cs that use parameterised queries. But consider these two invocations:


EXEC search_orders_1 @custid = 'ERNTC',
@fromdate = '19980205', @todate = '19980205'
EXEC search_orders_1 @custid = 'BOLSR',
@fromdate = '19980101', @todate = '19981231'

In Northgale, ERNTC is the top customer with 572 orders, whereas there is one single order for BOLSR. As you may guess, the best query plan for these two invocations are not the same. When searching for orders from a frequent customer in a short time span, it's probably better to the index on OrderDate, but when searching for orders from a infrequent customer in a broad time span, the index on CustomerID is likely to be better. How to deal with this? I will discuss a couple of tricks that you can employ.

Interpolating Some Values
While interpolating all parameter values into the string is a bad idea, it can sometimes make sense to interpolate some parameter values directly. Say that the search had included a @status parameter, and there had only been four possible values for Orders.Status, whereof Completed had accounted for 95 % of the values. A typical search may be for new orders, less than 1 % of the rows. In this case, it would be a very good idea to interpolate the value of the @status parameter into the query string, since different status values could call for different query plans. This applies not the least if there is an index on the Status column.
What about situation above, the combination of customer and search interval? Interpolating the customer id into the string is probably a bad idea, if you have millions of customers. That's one query plan per customer, so you would reduce the cache reuse drastically. The date interval is a better candidate, assuming that users would tend to use the same intervals during a day, for instance for the last seven days. You would still add quite a few more query plans to the cache though. But old plans for last week would age out by time, so unless the usage pattern is extremely diverse, this could work out.

Changing the Query Text
SQL Server looks up a query in the cache by hashing the query text. (Queries not in stored procedures and similar, that is.) This means that two queries with different text are different entries in the cache, even if they are logically equivalent. There are many ways to alter the query text, for instance you could do something like:


IF @fromdate IS NOT NULL AND @todate IS NOT NULL
BEGIN
SELECT @sql = @sql + CASE WHEN @fromdate = @todate
THEN ''
WHEN datediff(DAY, @fromdate, @todate) <= 7
THEN ' AND 2 = 2 '
WHEN datediff(DAY, @fromdate, @todate) <= 30
THEN ' AND 3 = 3 '
...

The advantage with changing the query text depending on the parameter values over interpolating them into the query string is that you get fewer plans in the cache, at the price of that in some cases you will not run with the best plan. To wit, you can only make some guesses where the breakpoint between two plans are. With some luck, the damage from this is limited. Even if the plan for a single day is not the best for the span of a week, it may still be decently useful. But you will have to know your data, and possibly tune as time goes.
Could this technique also be used to deal with the fact that different customers can have a very different number of orders? Probably not. Counting the number of orders for a customer before we construct the query is taking it too far in my opinion, and it could be more expensive than what you save in the other end.
As for how to altering the query text, there are many alternatives. With the current implementation in SQL Server, it even works with adding spaces or comments. And SQL Server MVP Adam Machanic pointed out to me that you can also play with the parameter list – as a matter of fact the list is also part of the query text.

Index Hints
I added this here, because I figured that sooner or later someone would ask Could you not use an index hint? Maybe there are situations where they would make sense, but then it would more be the case that if the user provides an order id, no matter which, then we always want the optimizer to use PK_Orders. I find it difficult to see that there are very many situations where it would be useful to add an index hint depending on the input value. As always, only add index hints, if you have identified a situation where the optimizer picks the wrong plan, and no other workaround is available.

OPTION (RECOMPILE)
In SQL 2005 (not in SQL 2000 and earlier) you can add this hint at the end of the query. This instructs SQL Server to recompile the query each time, and in this case SQL Server will not put the plan into cache.
If you feel that the input values can vary so wildly that you want a recompile each time, and yet want to use parameterised queries to be safe from SQL injection, then this option is a good choice. You could also add it selectively, so for instance you would not add it if an order id is given, but in all other cases.

[hassanf]

سلام
به کد زیر توجه کن


Create Procedure SelectTable(
@TableName nVarChar(100)
)
As Begin

--We use one sored procedure with table name paramer and dynamic sql
--instead of several procudre

Declare @sql nVarChar(1000)
Select @sql = 'Select *
From' + @TableName
Execute @sql
End





Create Procedure SelectNews
As Begin
Select *
From News
End

Create Procedure SelectArticle
As Begin
Select *
From Article
End

Create Procedure SelectMember
As Begin
Select *
From Member
End
.
.
.