Friday, July 2, 2010

Wednesday, February 17, 2010

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!





Friday, February 12, 2010

Me.. in a nutshell :)

Here I go about myself :)

an enthusiast with a penchant for knowledge (not sure if wisdom qualifies), a dreamer.

My inner self constantly pushes my outer physical carnation to keep hunting for amusement, challenges or things to look forward to OR in short to keep my uncontrollable and difficult self happily occupied with a sense of purpose.

Loves all the free rides around the sun. (25 so far and countingJ)


That’s all folks! .. just the highlights J