The Rabbit hole, Tips on Dynamic SQL

Leaving the Subway 

Ya never know what’s down the rabbit hole. 

I always try and warn people when you go down the Dynamic SQL path you have to be ready to go down the rabbit hole. You always think it’s just a simple thing until you’re face to face with the Queen of hearts and she yells “Off with her head!”There is one thing that Dynamic SQL is good at, Making you think out of the box.

So I’ll share some tips on Dynamic SQL  through a conversation I had with a friend. We’ll call him Bob.

Bob: hey why can’t I do this in Dynamic SQL, select @reccount = count ( * ) in dynamic sql?

Me: you have to declare the variable you are trying to set inside the Dynamic SQL, Remember Dynamic SQL is in another Process/Thread. So it cannot see cursors/variables outside its set of Dynamic SQL. If you run a USE statement in the Dynamic SQL it will only effect the statements inside the Dynamic SQL. To get something like a count between the two threads you would need to insert it into a temp table. I’ve listed a typical way to do this below.

--Original way that fails. 
DECLARE @cmd NVARCHAR(4000
DECLARE @reccount INT 

SET @cmd = 'Select @reccount = Count(*) from master..sysdatabases'

PRINT @cmd 
EXEC sp_executeSQL @cmd 

--Way that works by inserting to a temp table. 
DECLARE @cmd NVARCHAR(4000
DECLARE @reccount INT 
DROP TABLE
#rowcount
CREATE TABLE #rowcount 
  
(reccount INT NULL)

SET @cmd = 'Select Count(*) from master..sysdatabases'

PRINT @cmd 
INSERT INTO #rowcount (reccount
EXEC sp_executeSQL @cmd 

SELECT * FROM #rowcount

Bob: Ok so what about this problem I have of adding all these strings together this get’s really complicated and hard to read when I’m adding 4-5 variables together. Here’s an example.

DECLARE @cmd NVARCHAR(4000
DECLARE @startdate date
DECLARE @enddate date
DECLARE @dblevel INT 

SET @startdate = '02-01-10'
SET @enddate = '05-01-10'
SET @dblevel = 100

SET @cmd = 'Select * from master..sysdatabases where crdate between ''' + CONVERT(VARCHAR(25),@startdate) + ''' and ''' + CONVERT(VARCHAR(25),@enddate) + ''' and cmptlevel = ' + CONVERT(VARCHAR(10),@dblevel)
PRINT @cmd 

EXEC sp_executeSQL @cmd 

This is hard to read and I have to do lots of conversions to get things to work.

Me: I’ve found when dealing with Dynamic SQL it’s best to write the query you want with values and then replace the place holders you want to change. So taking your example here’s another way.

DECLARE @cmd NVARCHAR(4000
DECLARE @startdate date
DECLARE @enddate date
DECLARE @dblevel INT 

SET @startdate = '02-01-10'
SET @enddate = '05-01-10'
SET @dblevel = 100

SET @cmd = 'Select * 
from master..sysdatabases 
where 
crdate between ''<startdate>'' and ''<Enddate>''
and 
cmptlevel = ''<dblevel>'''

SET @cmd = REPLACE(@cmd,'<startdate>',@startdate)         
SET @cmd = REPLACE(@cmd,'<enddate>',@enddate)
SET @cmd = REPLACE(@cmd,'<dblevel>',@dblevel)
PRINT @cmd 
EXEC sp_executeSQL @cmd 

This is much easier to read and find what you’re looking for and what you need to replace. Now of course there’s many ways to do this for me this has always been easier.

BOB: Ok last question. Sometimes my Dynamic SQL doesn’t run. It doesn’t return anything and doesn’t seem to do anything at all. It won’t print it doesn’t error it just does nothing! It’s very frustrating. Any idea as to why this happens?

Me: This is where you need to be careful down the rabbit hole. Very careful, here’s an example of what will cause this exact situation to occur can you spot the difference and what the problem is?

DECLARE @cmd NVARCHAR(4000
DECLARE @startdate date
DECLARE @enddate date
DECLARE @dblevel INT 

SET @startdate = '02-01-10'
SET @enddate = '05-01-10'
SET @dblevel = NULL

SET @cmd = 'Select * 
from master..sysdatabases 
where 
crdate between ''<startdate>'' and ''<Enddate>''
and 
cmptlevel = ''<dblevel>'''

SET @cmd = REPLACE(@cmd,'<startdate>',@startdate)         
SET @cmd = REPLACE(@cmd,'<enddate>',@enddate)
SET @cmd = REPLACE(@cmd,'<dblevel>',@dblevel)
PRINT @cmd 
EXEC sp_executeSQL @cmd

I set the @dblevel = null. Anytime a variable is null and you place it into a Dynamic SQL string it nullifies the entire string. Worse yet it doesn’t error or complain about this issue it just does nothing. Go ahead and test it out on your system pass any NULL into a Dynamic SQL string and it will do nothing. You should make 100% sure that every variable/value that you pass into a Dynamic String is not a null.

This is just some quick tips I had for Dynamic SQL it’s by no means a full list of all the issues. I do warn people that with “Great power comes great responsibility” – Ya I went there.  :) Here’s some simple pro’s and con’s to keep in mind with Dynamic SQL

Pro’s

  • Great flexibility
  • Ability to get through many db’s without writing code that lives in each one. If you have many db’s on a server and you need to check X then there’s a way to do that.
  • They can now store execution plans. I still wouldn’t guarantee this but they previous to Sql 2005 Dynamic SQL never re-used plans.

Con’s

  • Prone to errors, it’s very hard to track errors and deal with errors in Dynamic SQL.
  • SQL Injection, this is the primary method to get SQL injection into your system. Protect and check your variables.
  • Readability, even with the sample I posted above it’s hard to read and figure out what Dynamic SQL is doing
  • Debugging/Testing, Debugging Dynamic SQL consists of many print statements and lots of trial and error to make sure you have everything just right.

I would also suggest that you look into PowerShell before going the Dynamic SQL route. Many of the things you would use Dynamic SQL for can now be done in PowerShell. Here are some Articles about PowerShell from Buck Woody and Aaron Nelson.

Regardless of what you decide be careful when you going down the Rabbit hole sooner or later you’ll be stuck in a big way with only a little door to get out from.

Thanks to @SqlVariant (Blog|Twitter) on the tips for Simple-Talk’s Prettify code.   It made the code samples in here look much better. 

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s