I have learned to detest code that builds dynamic SQL by repeated concatenation, especially with char functions for line breaks and tabs. A complex statement is difficult to read when it is pieced together with a bunch of CHAR, CAST, and CONVERT fuctions.
SQL is perfectly happy to interpret multiline strings with all line breaks and tabs included. I create a template of the complete SQL statement with tags for all variable parts, then use REPLACE functions to handle the modifications. This is especially useful when one variation requires changes in the field list, tables, and where clause. Another advantage of REPLACE is it can do implicit conversions to string of integers and avoid '...' + CAST(x AS VARCHAR) + '...'.
-- Basic templateSET @sql = 'SELECT fld1, fld2<fields> FROM tbl1 <joins> WHERE tbl1.xyz=0 <filters>'
-- One logical test controls additional fields, joins, and filter clauses-- All replaceable tokens are duplicated in the replacement strings to allow further actionsIF modification1 = 1 SET @sql = REPLACE(REPLACE(REPLACE(@sql, '<fields>', ', fld3, fld4<fields>'), '<joins>', 'INNER JOIN tbl2 ON tbl1.fld1 = tbl2.fld1 <joins>'), '<filters>', ' AND tbl2.pqr IS NOT NULL <filters>')-- Further modifications using the same logic-- Strip out any remaining tokens SET @sql = REPLACE(REPLACE(REPLACE(@sql, '<fields>', ''), '<joins>', ''), '<filters>', '')
In a tangential topic, I've built a process using dynamic sql which reads through all the databases on my servers to determine which indexes need reorganizing. Works great on all our servers, except for the ones with outside apps, where there the table owners are not dbo. I'm attempting to adapt the process to append the database owner name to the tablename -- building a variable ahead of time -- however I get a compile error whenever I have a variable in the "from" clause.
Is there a proper way to handle this that I'm missing?
Presumably you're getting the index info from sysindexes? Join to sysobjects to get the owner. It might look something like this:
I really enjoyed the article.
There is huge benefit for dynamic stored procedures in when vb.net and sql2000 work togeather;.
Thanks for the article. I am currently re-writing a chunk of my code because, in this case, it's going to give me a huge performance gain. The format I have settled on is:
DECLARE @query nvarchar(4000), -- // nvarchar using sp_executesql
set @LN = char(10) -- // Line Feed
set @TB = char(9) -- // Tab
set @LT = char(10) + char(9) -- // Line feed + Tab
set @query = ''
set @query = @query
+ 'select column1,' + @LT
+ 'column2' + @LN
+ 'from table1 (nolock)'
It is a little neater than having char() in lots of places.
One more time, SQL Server is happy to let you use multi-line strings with embedded tabs. The end-of-line is just white space to the SQL syntax analyzer, you don't have to close the string on each line and start the next with "+".
Instead of using all that concatenation, just use:
I can't enter a tab in this text box so there are four spaces before 'column2' instead of a tab, but you can use the tab character in Query Analyzer.
will someone please help and o'll wondering soul out by giving me a human version of (NOLOCK)??
NOLOCK aka READ UNCOMITTED aka "dirty read"
Allows reading a table in spite of any uncommitted transactions that may be in progress on other connections.
Pro: It saves time by not creating locks, and by not waiting for or blocking other transactions. It only requires a schema lock on the table, rather than creating locks for all the rows/pages/extents that are read. It ignores exclusive locks owned by other connections instead of waiting for those transactions to complete.
Cons: You can read data from partially completed transactions, then those transactions may be rolled back and the data disappears. For instance, you have a query WITH(NOLOCK) to sum the sales for each salesman to calculate commission payments. Just before you run this, someone begins a transaction showing they sold the Brooklyn Bridge for $2 billion. After your comission query runs and calculates a huge bonus for this guy, the transaction is rolled back and there is no trace of the data to explain what happened to the accountants.
It is very useful for reporting queries on production databases, if you know that either the data you're reading is static or you don't mind having counts and totals being a little off. A history table that sees INSERTs but no UPDATEs for example, or maybe a query summarizing last week's data when only today's data might be volatile. An inventory query that is checking whether there are less than 5000 #8 wood screws on hand to decide when to reorder is not going to notice the difference between 3875 and 3750.
It obviously is not recommended for accounting, or any application where exact numbers are required. An inventory query that is a little fuzzy about whether there should be 2 or 3 Ferraris on the showroom floor is a bad idea.