Exception Handling in SQL Server 2000 and 2005

Till the era of SQL Server 2000, there was no standard and structured way of exception handling in procedures return in SQL. Developers have to use the standard error variable “@@ERROR” to check if any error has occurred and take the necessary action.

A sample code in SQL Server 2000 will look like this:-

BEGIN TRANSACTION

--Write your statements here like “insert into some table”

set @Error = @@ERROR

--if there is some error

if @Error <> 0
Begin
ROLLBACK TRANSACTION
Else
COMMIT TRANSACTION
End
SQL Server 2005 have come with Try-Catch blocks, very similar to those used in frontline programming languages like C# and Java. Hence the same code above can be written in a much structured and efficient way in SQL Server 2005 as follows :-

BeginTry
BEGIN TRANSACTION
-- Do some operations here like “insert into some table”
COMMIT TRANSACTION
EndTry
BeginCatch
ROLLBACK TRANSACTION
EndCatch
Notice the important keywords here – “BeginTry”, “EndTry”, “BeginCatch” and “EndCatch”. These are equivalent of “Try-Catch” blocks used in C# or Java.

Passing two arguments in DataNavigateUrlFormatString in hyperlink field of .NET 2.0 Grid-View

In a hyperlink column of gridview, suppose the value of the property DataNavigateUrlFormatString is “ProductsForSupplierDetails.aspx?SupplierID={0}”. Many people think that the argument {0} is replaced by the value of the property DataKeyNames of the Gridview. But in actual case this is replaced by the value of the property DataNavigateUrlFields of the hyperlink column.

Now suppose you want to pass two arguments instead of one, something like this “ProductsForSupplierDetails.aspx?SupplierID={0}&CompanyName={1}”.

Now as these two arguments come from property DataNavigateUrlFields of the hyperlink column. So value of this property has to be “DataNavigateUrlFields="SupplierID,CompanyName”.

At run time the .NET replaces {0} with “SupplierID” and {1} with “CompanyName” of that row.

Pretty simple and straightforward it seems but I have seen many people struggling with this simple concept. Also I found wrong answers for this problem in some .NET forums. Hence thought to post it here for benefit of masses.

Renaming table column name in SQL Server

Well, that’s a pretty simple requirement if you are a database developer. Surprisingly I was only aware of how to do this using SQL Server Enterprise Manager. But one of my friends asked me how to do it using SQL Script. The requirement was that we cannot drop the existing table and recreate it again as the table has a lot of data. A little googling helped to figure out that it’s pretty simple.

There is an inbuilt system stored procedure “sp_rename” which can be used to accomplish this task. Infact this system stored procedure can be used to rename a user created object like table, column or user-defined data-type.

The syntax is pretty simple:-

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name', [ @objtype = ] 'object_type' ]

Eg. - To rename a column of table – “MyTable” from “OldColumnName” to “NewColumnName”, use following syntax:-

EXEC sp_rename 'MyTable.[OldColumnName]', 'NewColumnName', 'COLUMN'

And yes, that’s it. Pretty simple, but then you need to know it that something like this exists. :)