Feeds:
Posts
Comments

Archive for the ‘SQL Server’ Category

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.

~Mayank

Read Full Post »

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. 🙂

~Mayank

Read Full Post »