REPEATABLE READ

I hate Dynamic SQL. It's as lame as in-line SQL.

So I lose sleep when I'm 'forced' to use Dynamic-SQL to solve problems. Oh... I'll use it in an absolute pinch, but after the 'pinch' is over I try flipping the problem on its head over and over to see if I can come up with a non-cheesy solution.

That being said, a common place many people THINK they are forced into using Dynamic SQL comes when they need to dynamically create a new table. The following may not be the best example, but gets the point across: suppose you work for Acme, which sells widgets. All widget sales are stored in the Orders table (very creative naming going on at Acme, and in this example). Widget sales being what they are, the Orders table quickly gets big and concurrency would be effected if the data doesn't get routinely partitioned. To solve this issue, Acme has relied upon Outlook to make everything work for them. Once a month their 'DB Guy,' Bill, has had a reminder that tells him to go in and copy-paste-tweak some code into Query Analyzer that will copy all of the records out of Orders into a table with the following naming convention Orders_HIST_yyyy_mm. The idea is that a separate table will exist for every month, and tables greater than 12 months old can simply be dropped. The query is straight-forward, and the process relatively simple, only now you've inherited it and want to automate it.

A common approach to this scenario would be the following example (which can be run in the Northwind database). In the following example, a string is created to serve as the dynamically generated table name, then data is copied into a new table with the generated name -- but to accomplish this Dynamic SQL has been used:

DECLARE @archiveDate smalldatetime
SET @archiveDate = DATEADD(mm,-1,GETDATE())

DECLARE @archiveMonth char(2)
DECLARE @archiveYear char(4)

-- Getting at the month is goofy:
SET @archiveMonth = CAST(DATEPART(mm,@archiveDate) as varchar(2))
IF(LEN(@archiveMonth) < 2) SET @archiveMonth = '0' + @archiveMonth

SET @archiveYear = DATENAME(yyyy,@archiveDate)

DECLARE @tableName varchar(30)
SET @tableName = 'Orders_HIST_' + @archiveYear + '_' + @archiveMonth 


-- Execute the 'archival' process:
DECLARE @dynamicSQL varchar(800)

SET @dynamicSQL = 'SELECT * INTO ' + @tableName + ' FROM dbo.Orders ' +
	' WHERE orderDate < GETDATE() - 30 '

sp_execute @dynamicSQL		
		

The above example works. And in a simple scenario like Acme archiving widget sales I guess it would work and there wouldn't be too much of a need to get my panties in a wad about Dynamic SQL. But Dynamic SQL still always feels like a hack, and rarely will you see Dynamic-SQL employed in a solution as simple as the lame example that I've provided. Furthermore, in the real world Dynamic SQL is frequently housed in Stored Procedures which require input...input that could, theoretically at least, be prone to SQL-Injection due to the fact that Dynamic SQL is used (get rid of Dynamic SQL and SQL-Injection worries go away -- since you're not combining control-of-flow language with content). Below is a better way to solve the same problem, but without the use of Dynamic SQL. Because the following solution doesn't use Dynamic SQL, the factors are, at the very least, much better, and any issues with SQL-Injection are covered. Because the factors are better change to the process is much easier in the future -- another point that helps make the following solution an idiomatic solution.

Like the previous example, dynamic creation of the table name is a must, so that part of the batch is the exact same. The difference is that this solution crams copied data into a temporary table, which it then renames using sp_rename -- for an idiomatic solution:

DECLARE @archiveDate smalldatetime
SET @archiveDate = DATEADD(mm,-1,GETDATE())

DECLARE @archiveMonth char(2)
DECLARE @archiveYear char(4)

-- Getting at the month is goofy:
SET @archiveMonth = CAST(DATEPART(mm,@archiveDate) as varchar(2))
IF(LEN(@archiveMonth) < 2) SET @archiveMonth = '0' + @archiveMonth

SET @archiveYear = DATENAME(yyyy,@archiveDate)

DECLARE @tableName varchar(30)
SET @tableName = 'Orders_HIST_' + @archiveYear + '_' + @archiveMonth 

-- Execute the 'archival' process:
SELECT * INTO 
	tmpOrders_HIST 
FROM
	dbo.Orders
WHERE
	orderDate < GETDATE() - 30

EXEC sp_rename N'tmpOrders_HIST',@tableName		
		

The idiom may look a bit longer in my example, but that's just because I applied some more formatting to the single query in the batch that was doing the work. Well, that, and my example was lame in terms of real-world applicability -- I chose it to get the concept accross rather than to prove the need for such a solution; in the real-world we'd probably be doing something MUCH more complex than the simple SELECT INTO shown above.