Searching more then one Fields..?

UDA
N/A

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.
2 REPLIES
N/A

Re: Searching more then one Fields..?

hey dear TD master....
i think u are busy in holidays.

well,
i got the solution... Thanks ALLAH (GOD)

ALTER PROCEDURE [dbo].[sp_SearchCriteria]
-- Add the parameters for the stored procedure here
@order_id varchar(50)=NULL ,
@title_officer nvarchar(50)=NULL,
@Date varchar(50)=NULL,
@Date_TO varchar(50)=NULL,
@State_FIPS varchar(5)=NULL,
@County_FIPS varchar(5)=NULL,
@Order_Status nvarchar(MAX)=NULL,
@CompanyID varchar(50)=NULL,
@Service_Type varchar(50)=NULL
-- @Bill_Text varchar(50)=NULL,
--@Creation_Date varchar(50)=NULL
AS
BEGIN

-- @order_id is null and
-- @title_officer is null and
-- @Date is null and
-- @Date_TO is null and
-- @State_FIPS is null and
-- @County_FIPS is null and
-- @Order_Status is null and
-- @CompanyID is null and
-- @Service_Type is null

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @WHERE1 as varchar(8000)

SET @WHERE1 = ''

IF @order_id IS NOT NULL
BEGIN
SET @WHERE1 = '[ORDER].ORDER_ID = ''' + @order_id + ''''
-- SELECT @WHERE1 AS ASASAS
END
ELSE
BEGIN
SET @WHERE1 = 'ORDER_ID IS NOT NULL '
--SELECT @WHERE1
END
----------------------------------------------------------------------------------------------------------------
IF @Date IS NOT NULL and @Date_TO is not null
-- set @Date =cast(@Date as datetime)
-- set @Date_TO =cast(@Date_TO as datetime)
BEGIN
SET @WHERE1 = @WHERE1 + ' and Date_Created between '' ' + @Date + ''''+ ' '+'AND' +' '''+ @Date_TO +''''
--SET @WHERE1 = @WHERE1 +' AND [Date] LIKE ''' + @Date + '%'''
--select @WHERE1
END
-----------------------------------------------------------------------------------------------------------------

IF @title_officer IS NOT NULL
BEGIN
SET @WHERE1 = @WHERE1 + ' AND title_officer =''' + @title_officer + ''''
--SELECT @WHERE1
END

IF @Order_Status IS NOT NULL
BEGIN
SET @WHERE1 = @WHERE1 + ' AND Order_status =''' + @Order_Status + ''''
--SELECT @WHERE1
END

IF @Date IS NOT NULL and @Date_TO is null
BEGIN
SET @WHERE1 = @WHERE1 + ' AND cast(Date_Created as datetime) =''' + @Date + ''''
--SET @WHERE1 = @WHERE1 +' AND [Date] LIKE ''' + @Date + '%'''
END

IF @Date_TO IS NOT NULL and @Date is null
BEGIN
SET @WHERE1 = @WHERE1 + ' AND cast(Date_Created as datetime) =''' + @Date_TO + ''''
--SET @WHERE1 = @WHERE1 +' AND [Date] LIKE ''' + @Date + '%'''
END

IF @State_FIPS IS NOT NULL
BEGIN
SET @WHERE1 = @WHERE1 + ' AND state_Fips =''' + @State_FIPS + ''''
--SET @WHERE1 = @WHERE1 +' AND [Date] LIKE ''' + @Date + '%'''
END

IF @County_FIPS IS NOT NULL
BEGIN
SET @WHERE1 = @WHERE1 + ' AND County_FIPS =''' + @County_FIPS + ''''
--SET @WHERE1 = @WHERE1 +' AND [Date] LIKE ''' + @Date + '%'''
END

IF @CompanyID IS NOT NULL
BEGIN
SET @WHERE1 = @WHERE1 + ' AND Company_ID =''' + @CompanyID + ''''
--SET @WHERE1 = @WHERE1 +' AND [Date] LIKE ''' + @Date + '%'''
END

IF @Service_Type IS NOT NULL
BEGIN
SET @WHERE1 = @WHERE1 + ' AND Service_ID =''' + @Service_Type + ''''
--SET @WHERE1 = @WHERE1 +' AND [Date] LIKE ''' + @Date + '%'''
END

DECLARE @sqlstr1 NVARCHAR(1000)

-- Insert statements for procedure here
SET @sqlstr1 = 'SELECT [order_SubNo] ,'
SET @sqlstr1 = @sqlstr1+'title_officer, '
SET @sqlstr1 = @sqlstr1+ 'Date_Created, '
SET @sqlstr1 = @sqlstr1+'[Order_SubNo] ,'
SET @sqlstr1 = @sqlstr1+'stateName , '
SET @sqlstr1 = @sqlstr1+'CountyName , '
SET @sqlstr1 = @sqlstr1+'[user_id], '
SET @sqlstr1 = @sqlstr1+'[date_created] , '
SET @sqlstr1 = @sqlstr1+'order_Status , '
SET @sqlstr1 = @sqlstr1+'[date_updated] , '
SET @sqlstr1 = @sqlstr1+'[updateed_by_user_id] '
-- SET @sqlstr1 = @sqlstr1+' Excel_ID '
SET @sqlstr1 =@sqlstr1+ ' FROM view_3 WHERE '

EXEC (@sqlstr1 + @WHERE1)

--EXECUTE sp_executesql @sqlstr1
PRINT @sqlstr1 + @WHERE1

END

--Select * from view_3
N/A

Re: Searching more then one Fields..?

May be:

where
(@STATUS=0 or (@STATUS<>0 and dbo.[order].order_status=@STATUS))