Personal Tech Journal

To delete a function we need to use the drop function The Syntax is as follows DROP FUNCTION < FunctionName > 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 @< Variable_Name > TABLE >(< ColumnName1 > < Datatype >,< ColumnName2 > < Datatype >…) 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.

The exist() method returns a bit as follows 1, this means the XQuery expression in a query returns a result which is at least a xml node. 0, this means it’s a empty result NULL, this means that it was executed against a NULL value. The Syntax is exist (XQuery) The below shows how to check if the JobID 1 exists SELECT JobHistory.exist('/Job[@JobID=1]') FROM Employee

This performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value. The syntax is value (XQuery, SQLType) The below shows how to return the company name value of a varchar datatype SELECT JobHistory.value('(/Job/CompanyName)[1]','varchar(100)') FROM Employee

The Query() method is used to query the XML data using XQuery Expression The Syntax is query ('XQuery') The below shows how to display the xml node where jobid is 1 SELECT JobHistory.query('/Job[@JobID=1]') FROM Employee The below shows how to display the company name node where jobid is 1 SELECT JobHistory.query('/Job[@JobID=1]/CompanyName/text()') FROM Employee

We can use the xml data type methods to modify/query an XML stored in a column of xml datatype. The methods are as follows 1. Modify() 2. Value() 3. Query() 4. Exist() 5. Nodes() Today, we will see the use of Modify() method The Modify() method is used to modify parts of the XML data in the XML column using XQuery Expression The Syntax is modify (XML_DML) The below example shows to add one more job node to the xml UPDATE Employee SET JobHistory.modify( 'insert < Job JobID="2"> < CompanyName>News Corp< /CompanyName> < Years>1< /Years> < /Job> after (/Job)[1]') WHERE EMPLOYEEID = 1 The below example shows to replace a value in the xml node. UPDATE Employee SET JobHistory.modify( 'replace value of (/Job/@JobID)[1] with 3') WHERE EMPLOYEEID = 1 UPDATE Employee SET JobHistory.modify( 'replace value of (/Job/CompanyName/text())[1] with "BBC Corp"') WHERE EMPLOYEEID = 1 The below example shows to delete a n

The xml data type lets you store XML documents and fragments in a SQL Server database.  The below shows how to add a new column of XML datatype ALTER TABLE Employee ADD JobHistory XML The below shows how to Store the data in a XML column UPDATE Employee SET JobHistory= '< Job JobID="1" > < CompanyName>ABC Corp< /CompanyName> < Years>1< /Years> < /Job> ' WHERE EMPLOYEEID = 1

The "sp_executesql" is used for executing the sql statements that are build dynamically with or without parameters. The below example shows how to execute by dynamically passing the employee id. DECLARE @EmployeeNumber int; DECLARE @SQLString nvarchar(500); DECLARE @Parameter nvarchar(500); SET @SQLString = 'SELECT * FROM EMPLOYEE WHERE EMPLOYEEID = @EmployeeID'; SET @Parameter = '@EmployeeID int'; SET @EmployeeNumber = 2; EXECUTE sp_executesql @SQLString, @Parameter, @EmployeeID = @EmployeeNumber;

Dynamic SQL is nothing but SQL query that is generated programmatically. For example, we can get records of any table using the below Stored Procedure which implements Dynamic SQL CREATE PROC GETDETAILSBYTABLENAME(@TableName varchar(50)) AS BEGIN Declare @SQLQuery VarChar(5000) SELECT @SQLQuery = 'SELECT * FROM ' + @TableName Exec ( @SQLQuery) END

I am sure we commonly use SQL SERVER Reporting Services(SSRS)using the Data Source as the database. But if we have think to use XML, then one of the options to use the XML would be to host the XML's in a virtual directory in IIS. Then access the same as a connection string in SSRS. We can think of various options as well.