As I've already mentioned,
Nevertheless, there are three situations where you need to use
- 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
search_orders_2
In difference to sp_executesql,
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
Keep in mind that
The Use of Quotename()
As you can see, starting from line 46, I make frequent use of the built-in function (
, [
, <
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' SELECT quotename('abc{}def', '{') -- => {abc{}}def}
Of these examples, the interesting one in this context is the second. In '
). 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:
- 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. - 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.
- 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
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:
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
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
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,
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
Two qualifications here:
- 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.
- 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
- 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.
Comments