Wednesday, January 27, 2010

Common Table Expression Example

Hello Friends,
Here I put the sample code example using CTE.

--Generate Fibonacii Series using CTE


;WITH FibonaciiSeries(N,F1,F2,F3)
AS
(
SELECT CAST(1 AS BIGINT), CAST(1 AS BIGINT),CAST(0 AS BIGINT),CAST(1+0 AS BIGINT)
UNION ALL
SELECT N+1, F2,F3,F2+F3 FROM FibonaciiSeries WHERE N<92
)
SELECT * FROM FibonaciiSeries

--OUTPUT
-------------------------



--Find Factorial

;WITH Factorial(N,Number)
AS
(
SELECT CAST (1 AS BIGINT), CAST (1 AS BIGINT)
UNION ALL
SELECT N+1,CAST ((N+1)*(Number) AS BIGINT) FROM Factorial WHERE N<10
)

SELECT * FROM Factorial

--OUTPUT
-------------------------
Posted by at No comments:
Labels: , , , , , ,

Monday, January 25, 2010

Find the second highest salary for each department

Hello Friends,
Here I post one TSQL Challenge. I hopes it will help you.

TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

http://beyondrelational.com/blogs/tcb/archive/2009/10/13/tsql-beginner-s-challenge-1-find-the-second-highest-salary-for-each-department.aspx

TSQL Challenge Solved-

TSQL:-
SET NOCOUNT ON
DECLARE @Employees TABLE(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Adams','Finance', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('M Williams','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Miller','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('L Lewis','IT', 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Clerk','Back-Office', 10000)


SELECT * FROM @Employees

;WITH SecondHighestSalary
AS
(
SELECT EmployeeID,EmployeeName,Department,SALARY AS SALARY,RANK() over (partition by Department order by salary) AS SalaryNumber FROM @Employees
)
SELECT EmployeeID,EmployeeName,Department,SALARY FROM SecondHighestSalary WHERE SalaryNumber=2



OUTPUT:-

Posted by at No comments:
Labels: , , , , , ,

Tuesday, January 19, 2010

Use of Sys.Net.WebRequest

Hello Friends,
I read on article which can access web service using Sys.Net.WebRequest. Below is a read article URL i.e
http://www.dotnetfunda.com/articles/article480-accessing-webservice-by-using-sysnetwebrequest-.aspx

So I need to access handler using Sys.Net.WebRequest.

So here is a sample code to access handler using Sys.Net.WebRequest.

WebRequest.Aspx Page
---------------------------------------------

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<div>
<input type="text" id="text1" /><br />
<input type="button" id="Button1" value="Button" onclick="return Button1_onclick()" />
</div>
<div id="result">

</div>
</div>
<script language="javascript" type="text/javascript">
function Button1_onclick() {

var myRequest = new Sys.Net.WebRequest;
myRequest.set_url("sampleHandler.ashx");
myRequest.set_httpVerb("POST");
var tbvalue = document.getElementById("text1").value;
var body = "Param1=" + tbvalue;
myRequest.set_body(body);
myRequest.get_headers()["Content-Length"] = body.length;
myRequest.add_completed(myResultHandler);
myRequest.invoke();
}

function myResultHandler(executor, eventArgs) {
if (executor.get_responseAvailable) {
var result = document.getElementById("result");
result.innerHTML = executor.get_responseData();
}
}
</script>
</form>
</body>
</html>

Now Here is a code for 'sampleHandler.ashx'

<%@ WebHandler Language="C#" Class="sampleHandler" %>

using System;
using System.Web;

public class sampleHandler : IHttpHandler {

public void ProcessRequest (HttpContext context) {
string paramValue = string.Empty;
if (HttpContext.Current.Request.Form["Param1"]!="")
{
paramValue = HttpContext.Current.Request.Form["Param1"];
}
context.Response.ContentType = "text/plain";
context.Response.Write(paramValue);
}

public bool IsReusable {
get {
return false;
}
}

}

Happy Coding and Enjoy it...
Posted by at No comments:
Labels: , ,
Subscribe to: Posts (Atom)