Searching more then one Fields..?

UDA
Enthusiast

Searching more then one Fields..?

hi,
I have Tracking Search on my web application, I have 9 diffrent text boxes and two Date_Start and Date_End date-picker.
User has full rights to put any combination or gives all field, it up too user. we can not restrict.

now i hv to design a query that will run on 38 tb records. query should be efficient and 100% full fill the user requirments.
The same like query will also run on 12.6 tb db of MS-2008 server ...

I have write some part of query please provide the alternate if posiable.

Kalash
(Given Example is for SQL server-->> provide idea please
PLease reply on Arshad_4b@hotmail.com)
----------------------------------------------------------------------------------------------------------
USE [TataTest]
GO
/****** Object: StoredProcedure [dbo].[spTrackingOrders_Load1] Script Date: 09/27/2008 12:47:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spTrackingOrders_Load1]

@Client_ID as varchar(10),
@state_FIPS as varchar(15),
@county_FIPS as varchar(15),
@CompanyID as varchar(15),
@order_id as varchar(50),
@Service_Type as varchar(15),
@TitleOfficerName as varchar(100),
@STATUS as varchar(15),
@Date_To as datetime,
@Date_From as datetime

AS
SET NOCOUNT Off

DECLARE @WHERE1 as varchar(8000)
DECLARE @WHERE2 as varchar(8000)
DECLARE @WHERE3 as varchar(8000)
DECLARE @WHERE4 as varchar(8000)
DECLARE @WHERE5 as varchar(8000)
DECLARE @SORTLIST as varchar(8000)

--IF LEN(@order_id) > 0
-- BEGIN
-- if LEN(@Client_ID) > 0
-- BEGIN
-- SET @WHERE1 = ' AND i.Order_No LIKE ''%' + @order_id + '%'''
-- END
-- ELSE
-- BEGIN
-- SET @WHERE1 = ' AND i.Order_No LIKE ''' + @order_id + '%'''
-- END
-- END

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID) =0 And
len(@order_id) =0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID) >=1 And
len(@order_id) <=0 And
len(@Service_Type)< =0 and
Len(@TitleOfficerName)<=0 and
len(@STATUS) <=0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where [order].company_id =@CompanyID
end
--select @CompanyID
if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID) >=1 And
len(@order_id) =0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) <=''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS and dbo.[order].company_id =@CompanyID

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID) =0 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_id =@order_id
end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID) >=1 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) <=''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS and dbo.[order].company_id =@CompanyID and dbo.[order].order_id =@order_id
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID) >=1 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) <=''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].company_id =@CompanyID and dbo.[order].order_id =@order_id

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID) >=1 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) <=''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS and dbo.[order].company_id =@CompanyID and dbo.[order].order_id =@order_id

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID) =0 And
len(@order_id) =0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) <=''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type
end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID) =0 And
len(@order_id) = 0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS and service.service_id=@Service_Type

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID) >=1 And
len(@order_id) = 0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type and dbo.[order].company_id =@CompanyID
end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID) >=1 And
len(@order_id) =0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS and service.service_id=@Service_Type and dbo.[order].company_id =@CompanyID
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID) =0 And
len(@order_id) >=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type and dbo.[order].order_id =@order_id
end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID) =0 And
len(@order_id) >=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS and service.service_id=@Service_Type and dbo.[order].order_id =@order_id
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID) >=1 And
len(@order_id) >=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type and dbo.[order].order_id =@order_id and dbo.[order].company_id =@CompanyID
end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID) >=1 And
len(@order_id) >=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS and service.service_id=@Service_Type and dbo.[order].order_id =@order_id and dbo.[order].company_id =@CompanyID
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID) =0 And
len(@order_id) =0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].title_officer =@TitleOfficerName

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID) =0 And
len(@order_id) =0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS and dbo.[order].title_officer =@TitleOfficerName

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID) >=1 And
len(@order_id) =0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where [order].company_id =@CompanyID and dbo.[order].title_officer =@TitleOfficerName

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID) >=1 And
len(@order_id) =0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin

SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS and dbo.[order].title_officer =@TitleOfficerName and [order].company_id =@CompanyID

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].title_officer =@TitleOfficerName and dbo.[order].order_id =@order_id

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)=0 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS and dbo.[order].title_officer =@TitleOfficerName and dbo.[order].order_id =@order_id

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)= 0 and
len(@CompanyID)>=1 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].company_id =@CompanyID and dbo.[order].title_officer =@TitleOfficerName and dbo.[order].order_id =@order_id

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].company_id =@CompanyID and dbo.[order].title_officer =@TitleOfficerName and dbo.[order].order_id =@order_id and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id) =0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].title_officer =@TitleOfficerName and service.service_id=@Service_Type
end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)= 0 And
len(@order_id) = 0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type and dbo.[order].title_officer =@TitleOfficerName and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)>=1 And
len(@order_id) =0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type and dbo.[order].title_officer =@TitleOfficerName and dbo.[order].company_id =@CompanyID

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id) =0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin

SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type and dbo.[order].title_officer =@TitleOfficerName and dbo.[order].company_id =@CompanyID
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id) >=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type and dbo.[order].title_officer =@TitleOfficerName and dbo.[order].company_id =@CompanyID
and dbo.[order].order_id =@order_id

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)=0 And
len(@order_id) >=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type and dbo.[order].title_officer =@TitleOfficerName
and dbo.[order].order_id =@order_id and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)>=1 And
len(@order_id) >=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type and dbo.[order].title_officer =@TitleOfficerName and dbo.[order].company_id =@CompanyID
and dbo.[order].order_id =@order_id

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id) >=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) =0 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where service.service_id=@Service_Type and dbo.[order].title_officer =@TitleOfficerName and dbo.[order].company_id =@CompanyID
and dbo.[order].order_id =@order_id and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS
end

if ------------------------------(sTATUS sTARTED
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id) =0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)=0 And
len(@order_id) =0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)>=1 And
len(@order_id) =0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and dbo.[order].company_id =@CompanyID

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id) =0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and dbo.[order].company_id =@CompanyID and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and dbo.[order].order_id =@order_id

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)=0 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and dbo.[order].order_id =@order_id and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)>=1 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and dbo.[order].order_id =@order_id and dbo.[order].company_id =@CompanyID

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id) >=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and dbo.[order].order_id =@order_id and dbo.[order].company_id =@CompanyID
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id) =0 And
len(@Service_Type)> =1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and dbo.[order].title_officer =@TitleOfficerName
end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)=0 And
len(@order_id) =0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and service.service_id=@Service_Type
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)>=1 And
len(@order_id) =0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and dbo.[order].company_id =@CompanyID and service.service_id=@Service_Type

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id) =0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and dbo.[order].company_id =@CompanyID and service.service_id=@Service_Type
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id)>=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_id =@order_id and dbo.[order].order_status= @STATUS and service.service_id=@Service_Type

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)=0 And
len(@order_id)>=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_id =@order_id and dbo.[order].order_status= @STATUS and service.service_id=@Service_Type
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS
end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)>=1 And
len(@order_id)>=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_id =@order_id and dbo.[order].order_status= @STATUS and service.service_id=@Service_Type
and dbo.[order].company_id =@CompanyID

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id)>=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)=0 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_id =@order_id and dbo.[order].order_status= @STATUS and service.service_id=@Service_Type
and dbo.[order].company_id =@CompanyID and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id)=0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS and service.service_id=@Service_Type

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)=0 And
len(@order_id)=0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin

SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].company_id =@CompanyID
and dbo.[order].title_officer =@TitleOfficerName and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)>=1 And
len(@order_id)=0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].company_id =@CompanyID
and dbo.[order].title_officer =@TitleOfficerName

end

if
len(@state_FIPS)> =1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id)=0 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].company_id =@CompanyID
and dbo.[order].title_officer =@TitleOfficerName and
dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) = 0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id)>=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].order_id =@order_id
and dbo.[order].title_officer =@TitleOfficerName

end

if
len(@state_FIPS)> =1 and
len(@county_FIPS)>=1 and
len(@CompanyID)=0 And
len(@order_id)>=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].order_id =@order_id
and dbo.[order].title_officer =@TitleOfficerName
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)>=1 And
len(@order_id)>=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].order_id =@order_id
and dbo.[order].title_officer =@TitleOfficerName
and dbo.[order].company_id =@CompanyID

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id)>=1 And
len(@Service_Type) =0 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].order_id =@order_id
and dbo.[order].title_officer =@TitleOfficerName
and dbo.[order].company_id =@CompanyID
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id)=0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].order_id =@order_id
and dbo.[order].title_officer =@TitleOfficerName

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)=0 And
len(@order_id)=0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and service.service_id=@Service_Type
and dbo.[order].title_officer =@TitleOfficerName
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)>=1 And
len(@order_id)=0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and service.service_id=@Service_Type
and dbo.[order].title_officer =@TitleOfficerName and dbo.[order].company_id =@CompanyID

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id)=0 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and service.service_id=@Service_Type
and dbo.[order].title_officer =@TitleOfficerName and dbo.[order].company_id =@CompanyID
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS

end

if
len(@state_FIPS) =0 and
len(@county_FIPS)=0 and
len(@CompanyID)=0 And
len(@order_id)>=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].order_id =@order_id
and service.service_id=@Service_Type
and dbo.[order].title_officer =@TitleOfficerName

end

if
len(@state_FIPS) >=1 and
len(@county_FIPS)>=1 and
len(@CompanyID)=0 And
len(@order_id)>=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].order_id =@order_id
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS
and service.service_id=@Service_Type
and dbo.[order].title_officer =@TitleOfficerName
end

if
len(@state_FIPS) = 0 and
len(@county_FIPS)=0 and
len(@CompanyID)>=1 And
len(@order_id)>=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].order_id =@order_id
and service.service_id=@Service_Type
and dbo.[order].title_officer =@TitleOfficerName
and dbo.[order].company_id =@CompanyID
end

if
len(@state_FIPS) >= 1 and
len(@county_FIPS)>=1 and
len(@CompanyID)>=1 And
len(@order_id)>=1 And
len(@Service_Type) >=1 and
Len(@TitleOfficerName)>=1 and
len(@STATUS) >=1 and
(@Date_To) ='' and
(@Date_From) =''
Begin
SELECT CAST(dbo.[order].order_id AS varchar(10)) + ' ' + '/' + ' ' + CAST(dbo.[order].order_sub_id AS varchar(5)) AS [Order No],
dbo.[order].company_id AS [Company Code], dbo.[order].title_officer AS [Title Officer], dbo.state.statename, dbo.county.countyname, dbo.[user].user_id,
dbo.[order].date_created AS [Order Date], dbo.[order].order_status AS [Order Status], dbo.[order].date_updated AS [Last Updated],
dbo.[order].updateed_by_user_id AS [Last Updated By]
FROM dbo.state INNER JOIN
dbo.[order] ON dbo.state.state_fips = dbo.[order].state_fips INNER JOIN
dbo.county ON dbo.[order].county_fips = dbo.county.county_fips AND dbo.state.state_fips = dbo.county.state_fips INNER JOIN
dbo.[user] ON dbo.[order].user_code = dbo.[user].user_id INNER JOIN
dbo.feature INNER JOIN
dbo.service INNER JOIN
dbo.order_sub_items ON dbo.service.service_id = dbo.order_sub_items.service_id ON dbo.feature.feature_id = dbo.service.feature_id ON
dbo.[order].order_id = dbo.order_sub_items.order_id AND dbo.[order].order_sub_id = dbo.order_sub_items.order_sub_id LEFT OUTER JOIN
dbo.[user] AS user_1 ON dbo.[order].updateed_by_user_id = user_1.user_id
where dbo.[order].order_status= @STATUS
and dbo.[order].order_id =@order_id
and service.service_id=@Service_Type
and dbo.[order].title_officer =@TitleOfficerName
and dbo.[order].company_id =@CompanyID
and dbo.state.state_FIPS =@state_FIPS and county.county_fips =@county_FIPS
end
----------------------------------------------------------------------------------------------

Order No Company Code Title Officer statename countyname user_id Order Date Order Status Last Updated Last Updated By
------------------ ------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------- ----------------------- -------------------------------------------------- ----------------------- ---------------
10 / 1 NSQ john NEW JERSEY ESSEX 1 2008-09-19 22:45:49.263 Open NULL NULL

--------------------------------------------------------------------------
kind thanks.