What is function in SQL Server 2005/2008 ?

What is function in SQL Server 2005/2008 ?

Function is a database object in Sql Server. It is a set of sql statements that accepts only input parameters, perform actions and return the result.

 Function can return only single value or a table. We can’t use function to Insert, Update, and Delete records in the database table.

Basically two types of function in SQL Server.

1. System Defined Function.

2. User Defined Function

System Defined Function derived in two types.

1. System Defined Function.

1. Scalar Function

SQL scalar functions return a single value, based on the input value.

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

2. Aggregate Function

SQL aggregate functions return a single value, calculated from values in a column.

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

2. User Defined Function

User Defined Function derived in three types.

1. Scalar Function

User defined scalar function also returns single value as a result of actions perform by function. We return any data type value from function.

sno	city
1	Delhi
2	Kanpur
3	Lucknow
4	Aligarh
5	Alur
6	Ambaliyasan
7	Anandnagaar

select * from City

select * from State

sno	city	state
1	Lucknow	UP
2	Kanpur	UP
3	Anandnagaar	Assam
4	Ambaliyasan	Gujarat
5	Ambaji	Gujarat
6	Aligarh	UP
7	Delhi	Delhi
8	Alandha	Karnataka
9	Achhnera	UP

create function [dbo].[functionStatecity]

(

@state nvarchar(50),@city nvarchar(50)

)

returns nvarchar(250)

As

Begin return (Select @state + ' _ '+ @city);

end

Calling function in query:

Select dbo.functionStatecity(state,city) as Name, sno from state

Name	sno
UP _ Lucknow	1
UP _ Kanpur	2
Assam _ Anandnagaar	3
Gujarat _ Ambaliyasan	4
Gujarat _ Ambaji	5
UP _ Aligarh	6
Delhi _ Delhi	7
Karnataka _ Alandha	8
UP _ Achhnera	9

2. Inline Table Value Function

User defined Inline Table Value function return a table variable as a result of actions performs by function.

create function [dbo].[functioncity]()

returns Table

As

return (Select * from city where sno='4')

Calling function in query:

Select * from functioncity()

sno	city
4	Aligarh

3. Multi-Statement Table Value Function

Multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql stateme

What is DML, DDL, DCL and TCL ?

DML

DML is  Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

DDL

DDL is  Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL

DCL is Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL

TCL is Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

What is Option Explicit ?

When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time.

Difference between UNION and UNION ALL

UNION : UNION removes duplicate records in output. UNION performs a DISTINCT on the result set, eliminating any duplicate rows

UNION ALL : UNION ALL does not remove duplicates records in output

Select * from city

sno	city
1	Delhi
2	Kanpur
3	Lucknow
4	Aligarh
5	Alur
6	Ambaliyasan
7	Anandnagaar

select * from State

sno	city	state
1	Lucknow	UP
2	Kanpur	UP
3	Anandnagaar	Assam
4	Ambaliyasan	Gujarat
5	Ambaji	Gujarat
6	Aligarh	UP
7	Delhi	Delhi
8	Alandha	Karnataka
9	Achhnera	UP

select city from City

UNION

select city from State

city

Achhnera

Alandha

Aligarh

Alur

Ambaji

Ambaliyasan

Anandnagaar

Delhi

Kanpur

Lucknow

select city from City

UNION ALL

select city from State

city

Delhi

Kanpur

Lucknow

Aligarh

Alur

Ambaliyasan

Anandnagaar

Lucknow

Kanpur

Anandnagaar

Ambaliyasan

Ambaji

Aligarh

Delhi

Alandha

Achhnera

How to read a table without name in sql server

we can't create table without name or name as blank space, but after creating a table we can rename it with a blank space.

Sql Server doesn't support table name with two or more blank space, thus there is no resigns of conflict with name. 

now the queries for the table with name as blank space would be like this.

select * from []

Regards

Asit kumar

Merge Two XML Sheet By XSLT

CS page code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Xml;

using System.Xml.Xsl;

using System.IO;

using System.Configuration;

public partial class xsl_convert_html : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

       twoxml();

    }

    public void twoxml()

    {

        var xslt = new XslCompiledTransform();

        xslt.Load(Server.MapPath("~/merge.xslt"), new XsltSettings{EnableDocumentFunction = true}, null );

        var xmlr = XmlReader.Create(Server.MapPath("~/A.xml"));

        var xmlw = XmlWriter.Create(Server.MapPath("~/result.htm"));

        var xslargs = new XsltArgumentList();

        xslargs.AddParam("fileName", "", "B.xml");

        xslt.Transform(xmlr, xslargs , xmlw);

        xmlw.Close();

        xmlr.Close();

        Response.Redirect("result.htm");

    }

        

}

A.XML file:

<?xml version="1.0" encoding="utf-8" ?>

<Result>

  <A>

  <Id>A-001</Id>

  <Title1>A1 file</Title1>

  <Description>This is A1 xml file</Description>

    </A>

  <A>

  <Id>A-002</Id>

  <Title1>A2 file</Title1>

  <Description>This is A2 xml file</Description>

  </A>

  <A>

  <Id>A-003</Id>

  <Title1>A3 file</Title1>

  <Description>This is A3 xml file</Description>

  </A>

  <A>

  <Id>A-004</Id>

  <Title1>A4 file</Title1>

  <Description>This is A4 xml file</Description>

  </A>

</Result> 

B.XML file:

<?xml version="1.0" encoding="utf-8" ?>

<Result>

  <A>

  <Id>B-001</Id>

  <Title1>B1 file</Title1>

  <Description>This is B1 xml file</Description>

  </A>

  <A>

  <Id>B-002</Id>

  <Title1>B2 file</Title1>

  <Description>This is B2 xml file</Description>

  </A>

  <A>

  <Id>B-003</Id>

  <Title1>B3 file</Title1>

  <Description>This is B3 xml file</Description>

  </A>

</Result>

merge.xslt file:

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">

<xsl:output method="html" indent="yes"/>

<xsl:param name="fileName" select="'Result/*'" />

<xsl:param name="updates" select="document($fileName)" />

<xsl:variable name="updateItems" select="$updates/*" />

<xsl:template match="@* | node()">

  <xsl:copy>

    <xsl:apply-templates select="@* | node()"/>

  </xsl:copy>

</xsl:template>

<xsl:template match="/">

  <html>

    <body>

      <h2>Merge Two XML Sheet By XSLT</h2>

  <merge>

    <xsl:copy>

      <xsl:apply-templates select="Result/A"/>

      <xsl:apply-templates select="$updateItems"/>

        <table border="1">

          <tr bgcolor="#9acd32">

            <th>ID</th>

            <th>Title</th>

            <th>Description</th>

          </tr>

         

     <xsl:for-each select="Result/A">

      <tr>

        <td>

          <xsl:value-of select="Id"/>

        </td>

        <td>

          <xsl:value-of select="Title1"/>

        </td>

        <td>

          <xsl:value-of select="Description"/>

        </td>

      </tr>

      </xsl:for-each>

   

    </table>

    </xsl:copy>

  </merge>

  </body>

  </html>

</xsl:template>

</xsl:stylesheet>

OUTPUT:

How can convert XML file in to HTML format using by XSLT file.

What is XSLT ?

XSL stands for EXtensible Stylesheet Language, and is a style sheet language for XML documents.

XSLT stands for XSL Transformations. In this tutorial you will learn how to use XSLT to transform XML documents into other formats, like XHTML.

xsl_convert_html.aspx :

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="xsl_convert_html.aspx.cs" Inherits="xsl_convert_html" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://ask-dotnet.blogspot.in/">

<html xmlns="http://ask-dotnet.blogspot.in/">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

    

    </div>

    </form>

</body>

</html>

xsl_convert_html.cs :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Xml;

using System.Xml.Xsl;

using System.IO;

using System.Configuration;

public partial class xsl_convert_html : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

      xsl();

    

    }

    public void xsl()

    {

        //XmlReader loads XML file

        XmlReader reader = XmlReader.Create(Server.MapPath("~/xmlfile.xml"));

        //XmlTextWriter creats output file

        XmlTextWriter writer = new XmlTextWriter(Server.MapPath("~/xmlfile.htm"), null);

        //XslCompiledTransform loads XSLT file

        XslCompiledTransform xsl = new XslCompiledTransform();

        xsl.Load(Server.MapPath("~/test.xsl"));

        xsl.Transform(reader, writer);

        writer.Close();

        Response.Redirect("xmlfile.htm");

    }

}

XML File:

<?xml version="1.0" encoding="iso-8859-1"?>

<?xml-stylesheet type="text/xsl" href="test.xsl"?>

<category>

  <list>

    <sno>1</sno>

    <hotel>Uday Palace Hotel</hotel>

    <country>USA</country>

    <company>Columbia</company>

    <price>10.90</price>

    <year>1985</year>

  </list>

  <list>

    <sno>2</sno>

    <hotel>Hotel Sunstar Residency</hotel>

    <country>UK</country>

    <company>CBS Records</company>

    <price>9.90</price>

    <year>1988</year>

  </list>

  <list>

    <sno>3</sno>

    <hotel>Aura De Asia Hotel</hotel>

    <country>USA</country>

    <company>RCA</company>

    <price>9.90</price>

    <year>1982</year>

  </list>

</category>

test.xsl file:

<?xml version="1.0" encoding="iso-8859-1"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://ask-dotnet.blogspot.in/">

  <xsl:template match="/">

    <html>

      <body>

        <h2>Hotel  List</h2>

        <table border="1">

          <tr bgcolor="#FFCCFF">

            <th>Sno</th>

            <th>Hotel</th>

            <th>Country</th>

          </tr>

          <xsl:for-each select="category/list">

            <tr>

              <td>

                <xsl:value-of select="sno"/>

              </td>

              <td>

                <xsl:value-of select="hotel"/>

              </td>

              <td>

                <xsl:value-of select="country"/>

              </td>

            </tr>

          </xsl:for-each>

        </table>

      </body>

    </html>

  </xsl:template>

</xsl:stylesheet>

OUTPUT: