[SQL] Sample Complex Search - StoredProcedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Service.CPIC.GetFiles]
(
@PoliceDeptId int = NULL,
@FileType int = NULL,
@PoliceOfficerId int = NULL,
@FirstName nvarchar(50) = NULL,
@LastName nvarchar(50) = NULL,
@StartFileCreationDate datetime = NULL,
@EndFileCreationDate datetime = NULL,
@StartFileCompletionDate datetime = NULL,
@EndFileCompletionDate datetime = NULL
)
AS

DECLARE @MainQuery nvarchar(MAX)
DECLARE @Condition nvarchar(MAX)

SET NOCOUNT ON;

SET @Condition = ''
-- Main Query
SET @MainQuery = '
SELECT
[CPICId],
[File_ServiceId],
[CandidateId],
[ResultsId],
[IsRushJob],
[PositionOfTrust],
[AccurateRepresentation],
[CriminalRecords],
[FirstName],
[LastName],
[MiddleName],
[MaidenName],
[Aliases],
[Gender],
[DateOfBirth],
[BirthCity],
[Height],
[Weight],
[EyeColor],
[HairColor],
[Address],
[PreviousAddress],
[Status],
[Owner],
[PoliceDeptId],
[CheckedDateTime],
[CheckedByPoliceOfficerId],
[ConfirmedDateTime],
[ConfirmedByPoliceOfficerId],
[CheckOutById],
[FileType],
[DateTimeCreated],
[DateTimeCompleted]
FROM [dbo].[Service.CPIC.GetAllFiles]()
'


-- @PoliceDeptId
IF (@PoliceDeptId IS NOT NULL)
BEGIN
SET @Condition = 'WHERE [PoliceDeptId] = ' + CAST(@PoliceDeptId as nvarchar(max)) + ' '
END

-- @FileType
IF (@FileType IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [FileType] = ' + CAST(@FileType as nvarchar(max)) + ' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [FileType] = ' + CAST(@FileType as nvarchar(max)) + ' '
END
END

-- @PoliceOfficerId
IF (@PoliceOfficerId IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [CheckedByPoliceOfficerId] = ' + CAST(@PoliceOfficerId as nvarchar(max)) + ' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [CheckedByPoliceOfficerId] = ' + CAST(@PoliceOfficerId as nvarchar(max)) + ' '
END
END

-- @FirstName
IF (@FirstName IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [FirstName] LIKE ''' + @FirstName + '%'' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [FirstName] LIKE ''' + @FirstName + '%'' '
END
END

-- @LastName
IF (@LastName IS NOT NULL)
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE [LastName] LIKE ''' + @LastName + '%'' '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND [LastName] LIKE ''' + @LastName + '%'' '
END
END

-- @StartFileCreationDate AND @EndFileCreationDate
IF ((@StartFileCreationDate IS NOT NULL) AND (@EndFileCreationDate IS NOT NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCreationDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCreationDate,101) + ''' as datetime) '
END
END

IF ((@StartFileCreationDate IS NOT NULL) AND (@EndFileCreationDate IS NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCreated],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCreationDate,101) + ''' as datetime) '
END
END

-- @StartFileCompletionDate AND @EndFileCompletionDate
IF ((@StartFileCompletionDate IS NOT NULL) AND (@EndFileCompletionDate IS NOT NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCompletionDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) BETWEEN CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) AND CAST(''' + CONVERT(nvarchar(11),@EndFileCompletionDate,101) + ''' as datetime) '
END
END

IF ((@StartFileCompletionDate IS NOT NULL) AND (@EndFileCompletionDate IS NULL))
BEGIN
IF (@Condition = '')
BEGIN
SET @Condition = 'WHERE CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) '
END
ELSE
BEGIN
SET @Condition = @Condition + 'AND CAST(CONVERT(nvarchar(11),[DateTimeCompleted],101) as datetime) > CAST(''' + CONVERT(nvarchar(11),@StartFileCompletionDate,101) + ''' as datetime) '
END
END

PRINT @MainQuery + ' ' + @Condition
EXEC (@MainQuery + ' ' + @Condition)


/*
SELECT *
FROM [dbo].[Service.CPIC.GetAllFiles]()

-- @PoliceDeptId
WHERE [PoliceDeptId] IN
(
SELECT
CASE
WHEN @PoliceDeptId IS NULL THEN [Id]
ELSE @PoliceDeptId
END
FROM [Service.CPIC.PoliceDept]
)

-- @FileType
AND [FileType] IN
(
SELECT
CASE
WHEN @FileType IS NULL THEN [FileType]
ELSE @FileType
END
FROM
(
-- Awaiting Check
SELECT [FileType] = 1
UNION
-- Awaiting Confirmation
SELECT [FileType] = 2
) AS [FileTypes]
)

-- @FirstName
AND [FirstName] IN
(
SELECT
CASE
WHEN @FirstName IS NULL THEN [FirstName]
ELSE @FirstName
END
FROM [dbo].[Candidate]
)

-- @LastName
AND [LastName] IN
(
SELECT
CASE
WHEN @LastName IS NULL THEN [LastName]
ELSE @LastName
END
FROM [dbo].[Candidate]
)

-- @StartFileCreationDate
AND [


-- @EndFileCreationDate
*/

[SQL] Conversion DateTime DataType

Syntax for CAST:

CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example:

Format Date to mm/dd/yyyy
CONVERT(nvarchar(11),[FieldName],101)

From: '2007-05-04 16:35:52.140'
To: '05/04/2007'

Reseting Time Value to Zero
CAST(CONVERT(nvarchar(11),[FieldName],101) as datetime)

From: '2007-05-04 16:35:52.140'
To: '2007-05-04 00:00:00.000'

[SQL] Common Catalog Stored Procedures

1. sp_columns
Returns column information for the specified tables or views that can be queried in the current environment.

Example:

USE AdventureWorks
GO
EXEC sp_columns @table_name = N'Department',
@table_owner = N'HumanResources'

2. sp_tables
Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause.

Example:
USE AdventureWorks;GOEXEC sp_tables @table_name = '%', @table_owner = 'Person', @table_qualifier = 'AdventureWorks'

3. sp_databases
Lists databases that either reside in an instance of the SQL Server 2005 Database Engine or are accessible through a database gateway.

Example:
USE master;
GO
EXEC sp_databases;

[C#.Net] Session Class

public class SessionHelper
{
private const string POLICE_PROFILE = "POLICE_PROFILE";

public static PoliceOfficerDS PoliceProfile
{
get
{
if (HttpContext.Current.Session[POLICE_PROFILE] == null)
{
return new PoliceOfficerDS();
}
else return (PoliceOfficerDS)HttpContext.Current.Session[POLICE_PROFILE];
}
set
{
HttpContext.Current.Session[POLICE_PROFILE] = value;
}
}
}

[SQL] Complex WHERE Condition

WHERE [PoliceDeptId] IN
(
SELECT
CASE
WHEN @PoliceDeptId IS NULL THEN [Id]
ELSE @PoliceDeptId
END
FROM [Service.CPIC.PoliceDept]
)

-- @FileType
AND [FileType] IN
(
SELECT
CASE
WHEN @FileType IS NULL THEN [FileType]
ELSE @FileType
END
FROM
(
-- Awaiting Check
SELECT [FileType] = 1
UNION
-- Awaiting Confirmation
SELECT [FileType] = 2
) AS [FileTypes]
)

-- @FirstName
AND [FirstName] IN
(
SELECT
CASE
WHEN @FirstName IS NULL THEN [FirstName]
ELSE @FirstName
END
FROM [dbo].[Candidate]
)

-- @LastName
AND [LastName] IN
(
SELECT
CASE
WHEN @LastName IS NULL THEN [LastName]
ELSE @LastName
END
FROM [dbo].[Candidate]
)