Category Archives: SQL

SQL Search with multiple parameters (permutation and combination)

Posted on by

Create UDT in SQL and pass Datatable as parameter :

create type dbo.integers
AS
table (value int null)
go

create type dbo.strings
AS
table (value varchar(MAX) null)
go

————————————————–

IF EXISTS (
SELECT type_desc, type
FROM sys.procedures WITH(NOLOCK)
WHERE NAME = ‘GetData’
AND type = ‘P’
)
DROP PROCEDURE dbo.GetData
PRINT ‘Procedure GetData dropped’
GO
Create Proc GetData
@CID AS dbo.Integers READONLY,
@EmpID AS dbo.Integers READONLY ,
@Country AS dbo.stringss READONLY,
@StartDate nvarchar(MAX)=null,
@EndDate nvarchar(MAX)=Null
AS
if (len(@StartDate)=0)

Begin
select *,CC.FirstName from CCTable CD left outer join CLTbl C on CD.CLID=C.CLID
left outer join EmpTable E on CD.EmpID=E.EmpID
LEFT OUTER JOIN CCTable CC on CD.CID = CC.ID
where CD.CLID in ( SELECT * FROM @CID) and CD.EmpID in (Select * from @EmpID) and C.Country in (Select * from @Country)
order by CLDate desc
END
Else
Begin
Select * from (select CD.*,CC.FirstName,
E.EmpTable_Name from CCTable CD left outer join CLTbl C on CD.CLID=C.CLID
left outer join EmpTable E on CD.EmpID=E.EmpID
LEFT OUTER JOIN CCTable CC on CD.CID = CC.ID
where CD.CLID in ( SELECT * FROM @CID) and CD.EmpID in (Select * from @EmpID)
and C.Country in (Select * from @Country) )xx
where (xx.CLDate >= @StartDate and xx.CLDate <= @EndDate)
order by xx.CLDate desc
END

GO

IF EXISTS (
SELECT type_desc, type
FROM sys.procedures WITH(NOLOCK)
WHERE NAME = ‘GetData’
AND type = ‘P’
)
PRINT ‘Procedure GetData created’
GO

DataTable dtEmpID = new DataTable(“EmpID”);
dtEmpID.Columns.Add(“Value”, typeof(int));

if (Convert.ToInt32(cmbEmployee.CheckedItems.Count) == 0)
{
foreach (RadComboBoxItem item in cmbEmployee.Items)
{
dtEmpID.Rows.Add(Convert.ToInt32(item.Value));
}
}

————————————-

public DataTable GetData(DataTable dtClID, DataTable dtEmpID, DataTable dtCountryID, string StartDate, string EndDate)
{
cmd.Parameters.Clear();
cmd.CommandText = “GetData”;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = Connect();
cmd.Parameters.AddWithValue(“@ClID”, dtClID);
cmd.Parameters.AddWithValue(“@EmpD”, dtEmpID);
cmd.Parameters.AddWithValue(“@Country”, dtCountryID);
cmd.Parameters.AddWithValue(“@StartDate”, StartDate);
cmd.Parameters.AddWithValue(“@EndDate”, EndDate);
da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}

SQL Date between condition

Posted on by

Use the below query to get data between date range

Declare @FromDate Datetime
Declare @ToDate Datetime

Set @FromDate=’01-Jan-2001′

Set @ToDate =’15-Jan-2001′

Select * from (SELECT ID, Name, SubmittedDate )xx
where (xx.SubmittedDate >= @FromDate and xx.SubmittedDate <= @ToDate)                  order by SubmittedDate desc