Saturday, April 18, 2009

Able to use sp_executesql without declaring variables!

From Denis blog I came to know recently that we are able to use sp_executesql without declaring the variables!

It really sounded strange to me. I thought it to be one another bug :)

So thought would dig deep into this and see what best I can conclude.

--Dummy table schema
Create table tblQuestion1
(
sno int identity,
fname varchar(50)
)
Go

--Lets add some dummy records into the table
Insert into tblQuestion1 values ('Alpha')
Insert into tblQuestion1 values ('Beta')
Insert into tblQuestion1 values ('Gamma')

--So-called strange script
Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from tblQuestion1'
Exec sp_executesql @alpha = @strFetchData

If you have noticed we haven't declared @alpha in the script but SQL hasn't complained about it!

I thought the easiest way to understand would be to go through sp_executesql procedure.

Sp_helptext sp_executesql
go

Result - "(server internal)" … so its not a stored proc.

I tried adding another dummy parameter!

Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from sys.sysobjects'
Exec sp_executesql @alpha = @strFetchData, @beta = N''

Just the @ alone is also taken without a variable name:

Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from sys.sysobjects'
Exec sp_executesql @ = @strFetchData, @beta = N''

The code still works.

I tried adding the third parameter! hurray it fails.

Declare @strFetchData nvarchar(100)
Select @strFetchData = 'Select * from sys.sysobjects'
Exec sp_executesql @alpha = @strFetchData, @beta = N'', @gamma = N''

So I guess during compilation SQL engine is ignoring undeclared variables for the first 2 parameters but not from the third parameter onwards.

That said, syntax of sp_executesql on a higher level is:

1. First parameter - SQL Stmt
2. Second parameter - Parameter Definition
3. Third parameter and going fwd -- Value for the parameters are assigned here. (Parameter mapping)

So once parameter mapping comes into play it fails. The first two parameters is being taken as a dummy placeholders!!!

Few more examples:

--This works
Declare @IntVariable int;
Declare @SQLString nvarchar(500);
Declare @ParmDefinition nvarchar(500);

Set @SQLString =
N'Select EmployeeID, NationalIDNumber, Title, ManagerID
From AdventureWorks.HumanResources.Employee
Where ManagerID = @ManagerID';
Set @ParmDefinition = N'@ManagerID tinyint';

Set @IntVariable = 197;
Exec sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;

--This would fail
Declare @IntVariable int;
Declare @SQLString nvarchar(500);
Declare @ParmDefinition nvarchar(500);

Set @SQLString =
N'Select EmployeeID, NationalIDNumber, Title, ManagerID
From AdventureWorks.HumanResources.Employee
Where ManagerID = @ManagerID';
Set @ParmDefinition = N'@ManagerID tinyint';

Set @IntVariable = 197;
Exec sp_executesql @alpha = @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;

Saturday, April 11, 2009

How to create Google finance like Charts?

From the day I saw Google Finance i was thinking what tool they would be using to display those cool, user friendly charts! After hours of searching on the web I learned that they were using some sort of custom in-house build tool/logic.

Fair enough, but that doesn’t solve my problem. I wanted to create some graphs similar to that for few of my projects. On further ‘googling’, just recently I stumbled upon amCharts which has the similar charts like Google finance.

You can download and use all amCharts products for FREE. The only limitation of the free version is that a small link to this web site will be displayed in the top left corner of your charts. Btw, if you don’t want the link go ahead and purchase the product.

del.icio.us Tags: ,
  
Technorati Tags: ,

Friday, April 10, 2009

List of some essential software one would need!

1. Browser – IE 8.0 and/or Google Chrome

2. Anti-Virus – One of the lightweight software is AVG. Check it out @ http://free.grisoft.com/

3. Offline thesaurus and dictionary - http://wordweb.info/free/

This is a cool app which sits on your task bar. For finding meaning of a word just block the word and press CTRL + Right click on it.

4. Photo Organizer – I love Picasa and am using it for quite sometime now without any issues. http://picasa.google.com/download/

5. Creating / Editing blogposts - http://writer.live.com/

6. Zip Compression utility – I was using Winzip then WinRar but offlate I have started using 7-zip an open source project. Download it here http://www.7-zip.org/download.html

7. Instant Messaging – I was using MSN and Yahoo. But offlate I am using only Gtalk or the Chat feature which comes embedded within Gmail. May be you might want to try out Skype if you love talking more than typing :)

8. Download Manager – I use Free download Manager for quite sometime now. http://www.freedownloadmanager.org/download.htm but one of my friend told me about http://www.orbitdownloader.com/ (which I haven’t tried yet)

9. WinPatrol – I use this to manage my startup programs, services etc., kind of it sits on the system tray alert you to hijackings, malware attacks and critical changes made to your computer without your permission. http://www.winpatrol.com/

10. I do have Microsoft Windows Defender as well.

11. CCleaner is a freeware system optimization, privacy and cleaning tool. It removes unused files from your system - allowing Windows to run faster and freeing up valuable hard disk space. It also cleans traces of your online activities such as your Internet history. Additionally it contains a fully featured registry cleaner. But the best part is that it's fast (normally taking less than a second to run) and contains NO Spyware or Adware! :)

12. Automated Password Manager and Form filler - RoboForm http://www.roboform.com/php/land.php?affid=dnf01&frm=frame10

Along with it I have Microsoft Private Folder also installed in my laptop. I guess Microsoft is not giving it out anymore. Since I have downloaded it sometime in 2006 i still have it working and it helps me have few of movie collections securely :)

Let me know what you guys use or if I miss anything important which one should have in their box.

Technorati Tags:
  
del.icio.us Tags: