Tag Archives: Dynamic Result Sets

Using Select to Return Dynamic SQL Statements

I’ve always thought this was pretty slick…

We’ve all gone into Query Analyzer or SSMS and ran select, insert, or update  statements.  You write the statement, hit execute, and it returns the data or performs the action. 

What we’re doing here is no different except that we are actually going to use the SELECT statement to build the statements that we eventually want to capture and run.

The SELECT statement below simply returns the string you see between the single quotes:

dynamicsqlpic1-011420091

Lets take it a bit further.  The next image shows that we have modified the string, a bit, by adding a where clause to it.  We’ve also appended a variable (EmployeeID), a FROM clause, and a WHERE Clause to the end of the statement.  What we end up with are the three individual, select statements, shown, with the explicit EmployeeID dynamically tacked onto the end.

dynamicsqlpic2-01142009

You can then copy those statements into the SSMS window and execute them.

Admittedly, those examples are probably only useful as examples, but they illustrate a point.  We can use the SELECT statement to build, and more importantly SEE, the statements that we’re going to run.

Let’s make it an UPDATE statement: 

dynamicsqlpic3-01142009

Now, looking at the two resulting update statements, we can clearly see that we are about to give our two DBAs ten hours more vacation than anyone else in The Firm.  They clearly deserve it.  Simply run those two statements we’re off to Hawaii 🙂

Sometimes being a DBA means covering your tracks.  Actually, that’s one of the prime directives.  Covering your tracks can mean taking backups, verifying proper permissions are in place, making sure maintenance jobs are running, etc.  In this case, covering your tracks can mean “Knowing, and being able to document, the statements and parameters that are about to be executed”.  If I go to the trouble to build the statement…I will typically save it, and the results, for reference.  It may come in handy in the future too.

 This technique can also be usefull when troubleshooting failed insert or update statements.  If you’re not dealing too many rows you can quickly scan them or run them in batches to narrow in on the culprit.   

The examples above use the “Adventureworks” database that Microsoft provides for practicing and tutorials.  It’s not installed by default (Like Northwind and Pubs used to be for SQL 2000) so if you have SQL 2005 installed and want the AdventureWorks database, to practice in, you can down load it here at Codeplex