Did I say bugs?!?!

SQL is relatively simple to learn and the same goes out for Dynamic SQL. However, there a few things that might take you a while to figure out. When I came across these (and yes that was after working on MS SQL server for quite some time), my first reaction was bugs!! .. there are bugs in SQL!! And to be frank I was lil’ bit pleased with myself for figuring out these ‘bugs’ =)

Here we go,

declare @var1 varchar(30)

set @var1 = null -- implicit / not needed

…

-- some not so rosy code here

…

print ('And the fox ' + @var1)

And here's the output:

Whoa! There’s no output! What happended to the fox? Here’s the deal. MS SQL doesn’t print anything if any of the variable in the print string is empty. So, if you fail to initialize the variable, or while using dymanic SQL if one of the strings doesn’t not get the value, you get nothing from the print. It gets a bit more critical when you are trying to debug dynamic SQL. So as long as you are dealing with something like :

set @var1 = 'jumped over the moon..'

print ('And the fox ' + @var1)

you will be good. No clue about the foxy behavior of ‘print’. Is this an intended behavior in MS SQL !??! I have my doubts J. Better safe than wonder, I have now resorted to something like :

print ('And the foxy fox ' + isnull(@var1, ''))

To dig it further, this is a generic problem with strings, the following code gives no error, and no results! If I try to print ‘@Qry’ just before the exec, it will print nothing.

declare @tablename varchar(30)

declare @Qry varchar(100)

set @tablename = null

set @Qry = 'select top 5 * from ' + @tablename

exec (@Qry)

No output/no error! To ensure better handling, you need to put in explicit checks/error msgs. Something like

If(@tablename is not null)

begin

...

end

else begin

.. -- print 'Some error msg here'

end

Good luck & have fun with SQL!

Disclaimer: Okay okay maybe not bugs?! :) But sure an amusing behavior of SQL!