Difference Between Having and Where Clause in Sql Server
Both Having Clause and Where clause is used to filter the data coming from the Select statement, but still there are some differences between them. These difference are given below:-
To show the difference between the Where Clause and the Having clause we will going to use the table EmployeeDeptInfo whose create query statement is given below :-
Create table EmployeeDeptInfo ( Employeeid int, Departmentid int)
and it contains the following data
Employeeid Departmentid
1 1
2 2
3 2
4 3
3 2
2 2
5 4
2 2
1) Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.
For example, the sql query
Update EmployeeDeptInfo Set departmentid =7 Where employeeid=4
will work fine but the query
Update EmployeeDeptInfo Set departmentid =7 Having employeeid=4
will not work
2) We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.
For example, the sql query
select * from EmployeeDeptInfo where count(employeeid)>1
will not work but the query
Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1)
will work fine
3) Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).
For Example, in the below sql Query
select employeeid, departmentid from EmployeeDeptInfo where employeeid=5
the where clause will search the table EmployeeDeptInfo for the record whose employeeid is 5 and then show the output.
but in the below query,
Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having employeeid=5
the result are first grouped by the Group By Clause and then they become again filtered by the condition defined in the having clause. Sometime , like above both queries, we get the same result with the help of Where clause and having clause but which way is best is determined automatically by the optimizer and it select the best way of executing it.
Monday, 31 March 2014
Thursday, 27 March 2014
public string Gtrk_UserInformation(BGTrack UserInfo)
{
string referenceNumber = "";
Ocon = Oconnect.GetConnection();
SqlCommand Ocmd = new SqlCommand("GTRK_CREATETRANSACTIONINFO", Ocon);
Ocmd.CommandType = CommandType.StoredProcedure;
try
{
if (Ocmd.Connection.State == ConnectionState.Closed)
{
Ocmd.Connection.Open();
}
Ocmd.Parameters.Clear();
Ocmd.Parameters.AddWithValue("@GTRK_TRUCK_NO", UserInfo.TruckNo);
Ocmd.Parameters.AddWithValue("@GTRK_PLANT_CODE", UserInfo.Plantcode);
Ocmd.Parameters.AddWithValue("@GTRK_TRUCK_TYPE_CODE", UserInfo.TruckType);
Ocmd.Parameters.AddWithValue("@GTRK_TRANSPORTER_CODE", UserInfo.TransType);
Ocmd.Parameters.AddWithValue("@GRRK_REMARKS", UserInfo.Remarks);
Ocmd.Parameters.AddWithValue("@GTRK_DRIVER_NAME", UserInfo.DriverName);
Ocmd.Parameters.AddWithValue("@GTRK_LICENCE_NO", UserInfo.DrivLinceno);
Ocmd.Parameters.AddWithValue("@GTRK_MOBILE_NO", UserInfo.DriveMobileno);
Ocmd.Parameters.AddWithValue("@GTRK_CREATE_BY", UserInfo.Createby);
Ocmd.Parameters.AddWithValue("@FLAG", UserInfo.Flag);
Ocmd.Parameters.Add("@referenceNumber", SqlDbType.VarChar, 20).Value = "00000000";
Ocmd.Parameters["@referenceNumber"].Direction = ParameterDirection.Output;
Ocmd.CommandTimeout = 0;
Ocmd.ExecuteNonQuery();
referenceNumber = Ocmd.Parameters["@referenceNumber"].Value.ToString();
}
catch (Exception ex)
{
referenceNumber = "00000000";
throw ex;
}
finally
{
if (Ocmd.Connection.State == ConnectionState.Open)
{
Ocmd.Connection.Close();
}
}
return referenceNumber;
}
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.Services;
using System.Text;
using BussineLayer;
public partial class frm_Gtrk_Glass_Loading : System.Web.UI.Page
{
BussineLayer.BGTrack ObBLL = new BussineLayer.BGTrack();
string strApp = string.Empty;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
txttruckType.Attributes.Add("ReadOnly", "ReadOnly");
txtTrnsName.Attributes.Add("ReadOnly", "ReadOnly");
txtTruckno.Attributes.Add("autocomplete", "off");
txtTrnsName.Attributes.Add("autocomplete", "off");
txtDrivername.Attributes.Add("autocomplete", "off");
txtLicence.Attributes.Add("autocomplete", "off");
txtmobile.Attributes.Add("autocomplete", "off");
txtRemarks.Attributes.Add("autocomplete", "off");
}
}
public string GenerateReqNumber()
{
try
{
string GenReqNo;
Int64 NextNum;
DataTable DtGeneReqNo = new DataTable();
DtGeneReqNo = ObBLL.Get_Gtrk_Generatereqno();
if (DtGeneReqNo.Rows.Count > 0 && DtGeneReqNo.Rows[0]["DOC_NO"].ToString() != "")
{
GenReqNo = DtGeneReqNo.Rows[0]["DOC_NO"].ToString();
NextNum = Convert.ToInt64(GenReqNo);
NextNum = NextNum + 1;
GenReqNo = Convert.ToString(NextNum);
GenReqNo = GenReqNo.PadLeft(10, '0');
return GenReqNo;
}
else
{
GenReqNo = "0000000001";
return GenReqNo;
}
}
catch (Exception ex)
{
return "Error in Generation Request Numbers";
}
}
[WebMethod]
public static string TrlNo(string TruckNo)
{
string result = "";
BussineLayer.BGTrack ObBLL = new BussineLayer.BGTrack();
DataTable DtTruck = new DataTable();
ObBLL.TruckType = TruckNo.ToString().Trim();
DtTruck = ObBLL.Get_Gtrk_truck_Type(ObBLL);
if (DtTruck.Rows.Count > 0)
{
for (int i = 1; i <= DtTruck.Rows.Count; i++)
{
result = result + "
";
return result;
}
[WebMethod]
public static string TransName(string Transportname)
{
string result = "";
BussineLayer.BGTrack ObBLL = new BussineLayer.BGTrack();
DataTable DtTrans = new DataTable();
ObBLL.TransName = Transportname.ToString().Trim();
DtTrans = ObBLL.Get_Transport_Name(ObBLL);
if (DtTrans.Rows.Count > 0)
{
for (int i = 1; i <= DtTrans.Rows.Count; i++)
{
result = result + "
";
return result;
}
[WebMethod]
public static string TrackNumber(string TrackNo)
{
string result = "";
BussineLayer.BGTrack objBLL = new BussineLayer.BGTrack();
DataTable DtTruck = new DataTable();
objBLL.TruckNo = TrackNo.ToString().Trim();
DtTruck = objBLL.Get_Gtrack_tracknumber(objBLL);
if (DtTruck.Rows.Count > 0)
{
for (int i = 1; i <= DtTruck.Rows.Count; i++)
{
result = result + "
";
return result;
}
public void btnSave_Click(object sender, EventArgs e)
{
string[] Transnames = new string[100];
string[] TrackTypes = new string[100];
TrackTypes = txttruckType.Text.Split('-');
Transnames = txtTrnsName.Text.Split('-');
string DocNo = "";
string TruckTime = DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss");
DataTable DtInsInfor = new DataTable();
ObBLL.TruckNo = txtTruckno.Text.ToString().Trim();
ObBLL.Plantcode = Session["Client"].ToString();
ObBLL.TruckType = TrackTypes[0].ToString().Trim();
ObBLL.TransType = Transnames[0].ToString().Trim();
ObBLL.Remarks = txtRemarks.Text.ToString().Trim();
ObBLL.DriverName = txtDrivername.Text.ToString().Trim();
ObBLL.DrivLinceno = txtLicence.Text.ToString().Trim();
ObBLL.DriveMobileno = txtmobile.Text.ToString().Trim();
ObBLL.Createby = Session["UserName"].ToString();
if (Transnames[0].Length==10 && TrackTypes[0].Length==3)
{
ObBLL.Flag = "IN";
DocNo = ObBLL.Gtrk_UserInformation(ObBLL);
}
else
{
ObBLL.Flag = "OUT";
DocNo = ObBLL.Gtrk_UserInformation(ObBLL);
}
Resetvalue();
if (DocNo != "0000000000" && DocNo.Length != 0)
{
hdnDocNo.Value = DocNo;
hdnDate.Value = TruckTime;
ScriptManager.RegisterStartupScript(UPDGlass, this.GetType(), "Click", "SuccessMessage();", true);
}
else
{
ScriptManager.RegisterStartupScript(btnSave, btnSave.GetType(), "Failer", "javascript:alert('Wrong user information');", true);
}
}
private void Resetvalue()
{
txtTruckno.Text = "";
txttruckType.Text = "";
txtTrnsName.Text = "";
txtDrivername.Text = "";
txtLicence.Text = "";
txtmobile.Text = "";
txtRemarks.Text = "";
}
}
function SuccessMessage(){
if (!alert("Document Successfully Saved.(Document No:" + $(".hdnDocNo").val() +")\n\n"+ "Truck in Entry-Date & Time :" + $(".hdnDate").val() +"")){
var url="PrintDocumentNo.aspx?&DocNo=" + $(".hdnDocNo").val() + "";
window.open(url,'_blank','height=850,width=670,status=yes,toolbar=no,menubar=no,location=no,scrollbars=no,resizable=no,titlebar=no' );
}
}
| " + DtTruck.Rows[i - 1]["TRUCKTYPE"].ToString() + " |
| No Records Found... |
| " + DtTrans.Rows[i - 1]["TRANSNAME"].ToString() + " |
| No Records Found... |
| " + DtTruck.Rows[i - 1]["GTRK_TRUCK_NO"].ToString() + " |
| No Records Found... |
Thursday, 13 March 2014
CREATE PROCEDURE [dbo].[USP_REQUEST_INSERT]
(
@XML_INFO NVARCHAR(MAX) = NULL
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
---------------------------
DECLARE @REQUEST_XML INT
EXEC SP_XML_PREPAREDOCUMENT @REQUEST_XML OUTPUT, @XML_INFO
--1.------INSERT TO T_PROCUREMENT_HEADER--------
INSERT INTO T_EP_PROCUREMENT_HEADER(DOCNUM, IOR_NO, COST_CENTER, SIF_ENTITY_CODE, ESTABLISHMENT_ID, PURPOSE,CURRENCY,TOTAL_AMOUNT,TAX_AMOUNT,GROSS_AMOUNT, STATUS,APP_LEVEL, ENTERED_DATE, ENTERED_BY,REMARKS)
SELECT DOCNUM, IOR_NO, COST_CENTER,SIF_ENTITY_CODE, ESTABLISHMENT_ID, PURPOSE,CURRENCY,TOTAL_AMOUNT,TAX_AMOUNT,GROSS_AMOUNT,STATUS,
APP_LEVEL, getdate(), ENTERED_BY ,REMARKS
FROM OPENXML (@REQUEST_XML , '/EPROC/HEADER' , 1)
WITH
(
DOCNUM VARCHAR(22)'DOCNUM/text()' ,
IOR_NO VARCHAR(22)'IOR_NO/text()' ,
COST_CENTER VARCHAR(10)'COST_CENTER/text()' ,
SIF_ENTITY_CODE VARCHAR(10)'SIF_ENTITY_CODE/text()' ,
ESTABLISHMENT_ID VARCHAR(10)'ESTABLISHMENT_ID/text()' ,
PURPOSE VARCHAR(250)'PURPOSE/text()' ,
CURRENCY VARCHAR(10)'CURRENCY/text()' ,
TOTAL_AMOUNT VARCHAR(10)'TOTAL_AMOUNT/text()' ,
STATUS VARCHAR(15)'STATUS/text()' ,
APP_LEVEL VARCHAR(100)'APP_LEVEL/text()' ,
TAX_AMOUNT VARCHAR(10)'TAX_AMOUNT/text()' ,
GROSS_AMOUNT VARCHAR(10)'GROSS_AMOUNT/text()' ,
ENTERED_BY VARCHAR(10)'ENTERED_BY/text()' ,
REMARKS VARCHAR(250)'REMARKS/text()'
)
--2.-------------Insert to T_PROCUREMENT_DETAILS --------------
INSERT INTO T_EP_PROCUREMENT_DETAILS (DOCNUM,LINE_ITEM,TYPE,DESCRIPTION,EXP_CATEGORY,UOM,QUANTITY,UNIT_PRIZE,NET_AMOUNT)
SELECT DOCNUM, LINE_ITEM,TYPE,DESCRIPTION, EXP_CATEGORY, UOM, QUANTITY, UNIT_PRIZE,NET_AMOUNT
FROM
openxml(@REQUEST_XML, '/EPROC/DETAILS', 1) WITH
(
DOCNUM VARCHAR(10) 'DOCNUM /text()',
LINE_ITEM VARCHAR(9) 'LINE_ITEM /text()',
DESCRIPTION VARCHAR(150) 'DESCRIPTION /text()',
TYPE VARCHAR(10)'TYPE/text()' ,
EXP_CATEGORY VARCHAR(10)'EXP_CATEGORY/text()' ,
UOM VARCHAR(10) 'UOM /text()',
QUANTITY VARCHAR(15) 'QUANTITY/text()',
UNIT_PRIZE VARCHAR(15) 'UNIT_PRIZE/text()',
NET_AMOUNT VARCHAR(9) 'NET_AMOUNT/text()')
SELECT 'SUCCESS' AS OUTPUT
-- If we reach here , success!
COMMIT
END TRY
--END TRY--------------------------------------------------------------
BEGIN CATCH
-- Whoops , there was an error
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000) , @ErrSeverity int
SELECT @ErrMsg = error_message()
, @ErrSeverity = error_severity()
RAISERROR(@ErrMsg , @ErrSeverity , 1)
END CATCH
SET NOCOUNT OFF
END
Saturday, 8 March 2014
Linq Program
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
::LINQ PROGRAM::
Asp.Cs page (Linq Insert,Delete,Update)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
getdetails();
}
//Select Query
private void getdetails()
{
DataClassesDataContext cons = new DataClassesDataContext();
var cust = from custom in cons.customer_Headers
select new
{
custom.CustomerId,
custom.CustomerCode
};
gvlist.DataSource = cust;
gvlist.DataBind();
}
//Insert Query
protected void btnclick_Click(object sender, EventArgs e)
{
DataClassesDataContext cons = new DataClassesDataContext();
customer_Header sam = new customer_Header
{
CustomerCode = Convert.ToInt32(txtcustcode.Text),
CustomerId = txtcustid.Text
};
cons.customer_Headers.InsertOnSubmit(sam);
cons.SubmitChanges();
getdetails();
}
//Delete Query
protected void BtnDelete_Click(object sender, EventArgs e)
{
DataClassesDataContext cons = new DataClassesDataContext();
Int32 str = Convert.ToInt32(txtcustcode.Text);
customer_Header custs = (from cuss in cons.customer_Headers
where cuss.CustomerCode == (str)
select cuss).Single();
cons.customer_Headers.DeleteOnSubmit(custs);
cons.SubmitChanges();
getdetails();
}
//Update Query
protected void btnUpdate_Click(object sender, EventArgs e)
{
DataClassesDataContext cons = new DataClassesDataContext();
Int32 strs = Convert.ToInt32(txtcustcode.Text);
customer_Header custUp = (from cus in cons.customer_Headers where cus.CustomerCode == (strs) select cus).SingleOrDefault();
custUp.CustomerId = txtcustid.Text;
cons.SubmitChanges();
getdetails();
}
}
Wednesday, 5 March 2014
Connection String:
/b>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
namespace TierLayer
{
public class Connection
{
public static string StrConnSql;//For Sql
public SqlConnection GetConnection()
{
StrConnSql = ConfigurationManager.ConnectionStrings["Con"].ToString();
SqlConnection Connection = new SqlConnection(StrConnSql);
return Connection;
}
}
}
DAL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace TierLayer
{
public class DataAccessLayer
{
Connection con = new Connection();
public int Insertdate(BusinessProperties Bp)
{
int rows = 0;
SqlConnection conn = new SqlConnection();
conn = con.GetConnection();
SqlCommand cmd = new SqlCommand("tire_demo", conn);
cmd.CommandType = CommandType.StoredProcedure;
try
{
if (cmd.Connection.State == ConnectionState.Closed)
{
cmd.Connection.Open();
}
cmd.Parameters.AddWithValue("@Names", Bp.Name);
cmd.Parameters.AddWithValue("@Mobileno", Bp.Mobileno);
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
rows = 0;
throw ex;
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
return rows;
}
}
}
BLL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
namespace TierLayer
{
public class BusineeLogic
{
public int Insertdate1(BusinessProperties Bp)
{
DataAccessLayer DL = new DataAccessLayer();
try
{
return DL.Insertdate(Bp);
}
catch (Exception ex)
{
throw ex;
}
}
}
}
BEL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TierLayer
{
public class BusinessProperties
{
private string name;
public string Name
{
get { return name; }
set { name = value; }
}
private string mobileno;
public string Mobileno
{
get { return mobileno; }
set { mobileno = value; }
}
}
}
User Access Layer
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using TierLayer;
namespace _3tier_Arch
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnenter_Click(object sender, EventArgs e)
{
int rowss = 0;
BusinessProperties Bp = new BusinessProperties();
Bp.Name = txtname.Text;
Bp.Mobileno = txtmobile.Text;
BusineeLogic Bl = new BusineeLogic();
rowss= Bl.Insertdate1(Bp);
if (rowss.ToString()=="1")
{
ScriptManager.RegisterStartupScript(btnenter, btnenter.GetType(), "msg", "javascript:alert('Saved Successfully');", true);
}
}
}
}
User Access Layer
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="_3tier_Arch._Default" %>
3tier
Subscribe to:
Posts (Atom)