In the age of SQL injection attacks, the dangers of dynamic SQL are pretty well documented. But, as with most things, there's a time and a place for them. I recently indulged in a trip down memory lane, reviewing the scripts in my 'toolbox' that I use for administration, and I was reminded of how much I use dynamic SQL for my day-to-day administrative tasks. For many, this blog post isn't going to break any new ground. Consider it an homage to my favorite aspect of dynamic sql in t-sql; the ability to do administrative tasks en masse.
For example, at work we use SQL Agent extensively for plenty of administrative tasks. In those rare instance when one of those jobs should break, we like to know what happened in as much detail as possible. So, we use SQL Agent's step history to log that information to a text file and email the log file to us. But, when I'm building a large job, going into the Advanced page of the Job Step properties through SSMS and defining the output file for each step can get rather tedious.
Enter dynamic SQL. After I've built the job, I open up a Query window, and build a quick script like this:
DECLARE @sql varchar(max);
SET @sql = '';
SELECT @sql = @sql + 'EXEC msdb.dbo.sp_update_jobstep @job_id='''+CAST(job.job_id AS varchar(max))+''',@step_id='+CAST(step.step_id as varchar(10))+',@output_file_name=''G:\JobLogs\.log'',@flags=6'
FROM msdb.dbo.sysjobs AS job
JOIN msdb.dbo.sysjobsteps AS step
ON job.job_id=step.job_id
WHERE job.name = ''
AND step.step_id > 1;
EXEC(@sql);
As you can see, using the information from the SQL Agent system tables stored in msdb, I build a variable to string together executions of sp_update_jobstep in order to update all of the steps in my job, except the first one, to use the same log file, to append the step's history to that log file, and to also include the step information into SQL Agent's history, and then I execute that statement, which modifies all of the steps at the same time.
In jobs that have 10s, if not a 100 steps, I've saved myself quite a bit of time from having to go through the GUI.
Now,this certainly isn't the only way I could have done this task. I could also have scripted out the job through SSMS, done a Find/Replace to change the values for @flags and @output_file_name. but, since I'm weird and I also want my first step to NOT append to the log file because I want the file to be 'new' every time the job executes, I'd have to then go back and search through the code to find the first step and change the @flags value. Then, in SQL Server 2005, I'd have to drop the job and run the script to recreate. But, no, I think I like dynamic SQL just fine.