Personal Tech Journal

Exception Handling is a practice to capture the error efficiently and display the error in a well formatted way. In SQL Server, we use the TRY..CATCH Block for handling exceptions. The syntax is as follows BEGIN TRY       { sql_statement | statement_block } END TRY BEGIN CATCH       [ { sql_statement | statement_block } ] END CATCH [ ; ] The below shows an exception message for divide by zero error CREATE PROCEDURE GenerateException AS BEGIN BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT 'Error Occurred' END CATCH END

@@ERROR returns zero if the last executed statement did not throw an error, else it will return an error number. The below shows a error message with error number when an Divide by Zero is encountered. CREATE PROCEDURE ErrorNumber AS BEGIN DECLARE @ErrorNumber varchar(20) SELECT 1/0 SET @ErrorNumber = @@ERROR PRINT 'Error Generated : ' + @ErrorNumber END

RAISERROR will use the built-in messages in sys.messages view or dynamically generated error message. The Syntax is as follows RAISERROR ( { msg_id | msg_str | @local_variable }     { ,severity ,state }      [ ,argument [ ,...n ] ] )     [ WITH option [ ,...n ] ] Severity 0 – 18 , Can be raised by any user Severity 19 – 25, Can be raised by those who have sysadmin role. State : This is a integer value between 0 to 255 The below shows an error message if the employee id does not exists CREATE PROCEDURE GenerateError(@EmployeeID int) AS BEGIN If exists(SELECT 1 FROM employee WHERE EmployeeID = @EmployeeID) BEGIN SELECT * FROM Employee WHERE EmployeeID = @EmployeeID END ELSE BEGIN RAISERROR ('Employee ID does not exists', 10, 2) END END

To delete a function we need to use the drop function The Syntax is as follows DROP FUNCTION The below shows how to drop a function DROP FUNCTION USFORMATPHONE

We can modify the user defined functions by using the Alter command. The Syntax as shown below Scalar-valued Functions ALTER FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]   ] ) RETURNS return_data_type     [ WITH [ ,...n ] ]     [ AS ]     BEGIN         function_body         RETURN scalar_expression     END [ ; ] Table-valued Functions ALTER FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]   ] ) RETURNS @return_variable TABLE     [ WITH [ ,...n ] ]     [ AS ]     BEGIN         function_body         RETURN     END [ ; ] The below shows how a function has been modified to change the message ALTER FUNCTION USFORMATPHONE(@PhoneNumber varchar(25)) RETURNS Varchar(50) AS BEGIN DECLARE @phone varchar(50) IF(LEN(@PhoneNumber) = 10) SET @phone = '('+ SUBSTRING(@PhoneNumber,1,3) + ')...

We will look into the Table-Valued User Defined Function The syntax for Table-Valued is as follows CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     [ = default ] [READONLY] }     [ ,...n ]   ] ) RETURNS @return_variable TABLE      [ WITH [ ,...n ] ]  [ AS ]      BEGIN          function_body          RETURN      END [ ; ] The below example shows how to create a multistatement table-valued function which provides details based on the department status. CREATE FUNCTION DEPT_DETAILS_BY_STATUS(@Status varchar(10)) RETURNS @DEPTSTATUS TABLE ( DEPTID int, DEPTNAME varchar(100), STATUS varchar(10) ) BEGIN INSERT INTO @DEPTSTATUS Select * from Dept where Status = @Status RETURN END The usage of the same is as follows Select EmployeeID, FirstName, LastName, Dt.DeptName from Employee INNER JOIN DEPT_DETAILS_BY_STATUS('Active') Dt ON Dt.DeptID = Employee.DeptID

I received this mail and i thought its really worth to share. JESUS VS. Satan Satan went to visit Jesus in the Garden of Eden, and Satan came all happy and boasting. (Lk 4: 1-12; Job 1: 6-12) "Yes, Lord, now I have everybody captive, (well nearly everybody down there). I set traps, I used the baits of temptation, I know well what each one of them can't resist. I nearly caught them all!" (I Pet 5: 8-9; Eph 6: 10-17) "What are you going to do with them?" Jesus asked. and He was praying to God the Father. Satan answered "Oh, I am going to have some fun with them!" I'll make them divorce after they have married so the foundation of humanity will never be able to be established "the family" (Mt 19: 4-6; Mal 2: 16 ) I will make them hate each other and abuse each other, make them fall into alcohol and drugs without control. (Rom 13: 12-14) I will teach them to make weapons and bombs, so they kill each other "I am really going to have...

We will look into the Scalar-Valued User Defined Function The syntax for Scalar-Valued is as follows CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type     [ = default ] [ READONLY ] }     [ ,...n ]   ] ) RETURNS return_data_type     [ WITH [ ,...n ] ]      [ AS ]      BEGIN          function_body          RETURN scalar_expression      END [ ; ] The below example shows how a function returns the phone number in US Phone format (XXX)XXX-XXXX CREATE FUNCTION USFORMATPHONE(@PhoneNumber varchar(25)) RETURNS Varchar(50) AS BEGIN DECLARE @phone varchar(50) IF(LEN(@PhoneNumber) = 10) SET @phone = '('+ SUBSTRING(@PhoneNumber,1,3) + ')' + SUBSTRING(@PhoneNumber,4,3) + '-' + SUBSTRING(@PhoneNumber,7,4) ELSE SET @phone = 'Invalid Phone Number' Return @phone END The usage of the same is as follows SELECT EMPLOYEE.DBO.USFORMATPHONE ('1234567890')

This afternoon had been for a team lunch with my colleagues to Benito's . It was a nice place with some great food. Especially there Benito's special soup, sizzlers .. . A good place to visit..

User-Defined Functions are nothing but a program that accepts parameters and returns a type of result. There are two types of User Defined Functions 1. Scalar-Valued 2. Table-Valued

1. Table variables are similar to the normal tables or temporary tables. 2. This is good if you don’t want to store large data for temporary use. 3. These are only available during the execution of the program. 4. The Syntax is as follows DECLARE @ TABLE >( , …) 5. The below shows the example to use table variables DECLARE @temp_employee TABLE(EmployeeID int PRIMARY KEY, FirstName varchar(50), LastName varchar(50), Sex char(1), Age int DEFAULT 18, DateOfBirth DateTime) SELECT * FROM @temp_employee

1. Temporary Tables are similar to tables unlike they are life is only for the current session and it will be automatically dropped after that. 2. This is very useful for storing some information during a large computations temporarily instead of using physical tables. 3. They cannot have foreign key constraints. 4. It is stored in the TempDB. 5. The Syntax is CREATE TABLE #< TableName >(< ColumnName1 > < Datatype >,< ColumnName2 > < Datatype >…) 6. The below shows an example how to create a temporary table CREATE TABLE #tempEmployees(EmployeeID int PRIMARY KEY, FirstName varchar(50), LastName varchar(50), Sex char(1), Age int CONSTRAINT df DEFAULT 18, DateOfBirth DateTime)

1. Derived Tables are nothing but using a result of a query as a table. 2. This is much better than using temporary tables. 3. This increases the performance. 4. The below shows how to return employee and department info for those who don’t belong to a inactive department Select EmployeeID, FirstName, LastName, Dt.DeptName from Employee INNER JOIN (Select * from Dept where Status !='InActive') Dt ON Dt.DeptID = Employee.DeptID

1. Common Table Expressions are similar to that of a Derived Table. 2. We can define a CTE and use it multiple times in a query unlike Derived Tables. 3. The number of columns in the expression should be similar to that of the query result used in CTE. 4. CTE can be used recursively also. The Syntax is as follows WITH expression_name [ ( column_name [,...n] ) ] AS ( CTE_query_definition ) The below example demonstrate the use WITH Dt_CTE (DeptID, DeptName, Status) AS (Select * from Dept where Status !='InActive') Select EmployeeID, FirstName, LastName, Dt.DeptName from Employee INNER JOIN Dt_CTE Dt ON Dt.DeptID = Employee.DeptID

The Limitations for XML Datatype is as follows 1. The size of the XML datatype cannot exceed 2GB. 2. We cannot cast or convert to text or ntext. 3. The column of XML datatype cannot be sorted or we can not group the same using GROUP BY. 4. This cannot be passed as parameters to be used with scalar or built-in function except with ISNULL, COALESCE, and DATALENGTH. 5. This cannot be used as a column that can have an Index. But we can add a non-clustered index using the INCLUDE keyword.

Office 2010 and Sharepoint 2010 Service Pack 1 is released. Check out here http://blogs.technet.com/b/office_sustained_engineering/archive/2011/06/28/announcing-office-2010-and-sharepoint-2010-service-pack-1-availability.aspx

When working with a RadioButtonList control and if you wish to disable one of the list items in it , the very obvious solution would be as mentioned below radioList.Items[1].Enabled = false; But to surprise this does not work. Bingo...the solution to this problem is to use as mentioned below radioList.Items[1].Attributes.Add("disabled", "disabled"); Happy Coding....

The nodes() method is useful when you want to shred an xml data type instance into relational data means it will be mapped into a new row. The syntax is nodes (XQuery) as Table(Column) The below shows how to display the xml data in relational data in different rows. SELECT JobHistory.query('.')as nodes FROM Employee CROSS APPLY JobHistory.nodes('/Job') as MyNodes(a)

The Microsoft SQL SERVER code name "Denali" CTP 3 is available to download at http://technet.microsoft.com/en-US/evalcenter/hh225126.aspx

I received a invite for Google+ from my friend. On it now.. Exploring and i would say its a welcome change from Facebook. Lets explore more.