Solved

SQL Search Query not returning expected results

Posted on 2008-10-29
8
234 Views
Last Modified: 2012-05-05
Hi Experts!
I am hoping this will be a quick fix.  I only need you to look at the "Search of FEIN field"  if this one will work then I will be able to get the Search ERN field to work.  I have an interface that has multiple checkboxes that will generate a query based on what the user selects.  Everything works fine except for if the user actually types in a federal id number (FEIN) or an employer registration number (ERN).  The data is in the database but nothing is return.  The rest of the query works perfectly!  Does anyone have any suggestions on how to fix my problem.  Both FEIN and ERN are varchar fields however the data is numeric.  It is the same as a social security number.  

Please let me know if I need to be a bit clearer on what I need.

Thanks
Miracle By Design
alter Procedure [dbo].[usp_Sel_ERSSearch]

(

    @Criteria varchar(1000),

	@SearchValue1 int = null, --FEIN checkbox value

	@SearchValue2 int = null, --ERN checkbox value

	@SearchValue3 int = null, --Legal Name checkbox value

	@SearchValue4 int = null, --DBA Name checkbox value

	@SearchValue5 int = null, --Owner/Officer checkbox value

	@SearchValue6 int = null  --Address checkbox value

)

AS

 

 SET NOCOUNT ON

 Begin	

	  Begin Transaction

		--Search of FEIN field

		If @SearchValue1 = 1 and @SearchValue2 = 0 and @SearchValue3 = 0 and @SearchValue4 = 0 and @SearchValue5 = 0 and @SearchValue6 = 0 

		Begin

			

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (FEIN = @Criteria and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (FEIN = @Criteria and a.Confirmation is not null)

			

				Order By FEIN
 

		End

		--Search on ERN field

		If @SearchValue2 = 1 and @SearchValue1 = 0 and @SearchValue3 = 0 and @SearchValue4 = 0 and @SearchValue5 = 0 and @SearchValue6 = 0

		Begin
 

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (ERN = @Criteria and a.JTAppID is not null)

			

				

			Union all
 

				Select  Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (ERN = @Criteria and a.Confirmation is not null)

			

				Order By FEIN
 

		End

		--Search on Legal Name, DBA Name, Owner/Officer, Address fields

		If @SearchValue3 = 1 and @SearchValue4 = 1 and @SearchValue5 = 1 and @SearchValue6 = 1 and @SearchValue1 = 0 and @SearchValue2 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%'

					   Or DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%'

					   Or DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN

		End

		--Search on Legal Name, DBA Name, Owner/Officer fields

		If @SearchValue3 = 1 and @SearchValue4 = 1 and @SearchValue5 = 1 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%'

					   Or DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd 

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%'

					   Or DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN

		End

		--Search on Legal Name, DBA Name fields

		If @SearchValue3 = 1 and @SearchValue4 = 1 and @SearchValue5 = 0 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				 Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%'

					   Or DBAName Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%'

					   Or DBAName Like '%'+@Criteria+'%'and a.Confirmation is not null)

			

				Order By FEIN

		End

		--Search on Legal Name field

		If @SearchValue3 = 1 and @SearchValue4 = 0 and @SearchValue5 = 0 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN

		End 

		--Search on Legal Name, Owner/Officer fields

		If @SearchValue3 = 1 and @SearchValue5 = 1 and @SearchValue4 = 0 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd 

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE ( LegalName Like '%'+@Criteria+'%'

					   Or OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd 

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%'

					   or OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN

		End

		--Search on Legal Name, Address fields

		If @SearchValue3 = 1 and @SearchValue6 = 1 and @SearchValue5 = 0 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%'

					   Or Address Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (LegalName Like '%'+@Criteria+'%'

					   Or Address Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN

		End

		--Search on DBA Name, Owner/Officer, Address fields

		If @SearchValue4 = 1 and @SearchValue5 = 1 and @SearchValue6 = 1 and @SearchValue1 = 0 and @SearchValue2 = 0 and @SearchValue3 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE ( DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE ( DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN

		End

		--Search on DBA Name, Owner/Officer fields

		If @SearchValue4 = 1 and @SearchValue5 = 1 and @SearchValue6 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0 and @SearchValue3 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE ( DBAName Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE ( DBAName Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN
 

		End

		--Search on DBA Name, Address fields

		If @SearchValue4 = 1 and @SearchValue6 = 1 and @SearchValue5 = 0 and @SearchValue3 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (DBAName Like '%'+@Criteria+'%' Or Address Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN

		End

		--Search on DBA Name field

		If @SearchValue4 = 1 and @SearchValue5 = 0 and @SearchValue6 = 0 and @SearchValue3 = 0 and @SearchValue1 = 0 and @SearchValue2 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd 

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (DBAName Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (DBAName Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN

		End

	

		--Search on Owner/Officer, Address fields

		If @SearchValue5 = 1 and @SearchValue6 = 1 and @SearchValue1 = 0 and @SearchValue2 = 0 and @SearchValue3 = 0 and @SearchValue4 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE ( Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE ( Address Like '%'+@Criteria+'%' or OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN

		End

		--Search on Owner/Officer field

		If @SearchValue5 = 1 and @SearchValue1 = 0 and @SearchValue2 = 0 and @SearchValue3 = 0 and @SearchValue4 = 0 and @SearchValue6 = 0

		Begin

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE ( OFullName Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE ( OFullName Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN
 

		End

		--Search on Address field

		If @SearchValue6 = 1 and @SearchValue1 = 0 and @SearchValue2 = 0 and @SearchValue3 = 0 and @SearchValue4 = 0 and @SearchValue5 = 0 

		Begin
 

				Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (Address Like '%'+@Criteria+'%' and a.JTAppID is not null)

			

				

			Union all
 

				Select Distinct a.AssignmentID, a.Confirmation as RegNumber, ERN, FEIN, 

				LegalName, DbaName, Address, 

				OFullName, assn.AssignmentStatusDsc, a.AssignmentType, 

				u.UserID, (u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  

				Join Assignment a on a.JtAppID = es.JtAppID 

				Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd

				Left Join AdminUsers u on u.UserID = a.UserID 

				WHERE (Address Like '%'+@Criteria+'%' and a.Confirmation is not null)

			

				Order By FEIN
 
 

		End

		

		

	Commit Transaction

Open in new window

0
Comment
Question by:MiracleByDesign
8 Comments
 
LVL 8

Expert Comment

by:k_rasuri
ID: 22836217
did u check the FEIN query directly providing the values...by replacing @Criteria with the actual value. do you see the results coming up.

Select Distinct a.AssignmentID, a.JTAppID as RegNumber, ERN, FEIN,
                        LegalName, DbaName, Address,
                        OFullName, assn.AssignmentStatusDsc, a.AssignmentType,
                        u.UserID,(u.FirstName + ' ' + u.LastName) as UserName FROM ERS_Search es  
                        Join Assignment a on a.JtAppID = es.JtAppID
                        Left Join AssignmentStatus assn On assn.AssignmentStatusCd = a.AssignmentStatusCd
                        Left Join AdminUsers u on u.UserID = a.UserID
                        WHERE (FEIN = @Criteria and a.JTAppID is not null)
0
 

Author Comment

by:MiracleByDesign
ID: 22836285
Yes, I did replace the FEIN with the @Criteria and I still could not pull up the results I needed.  Thanks for the suggestion!
0
 
LVL 1

Expert Comment

by:miketob
ID: 22836754
Hello,
Only a suggestion for improved readability (but certainly not as fast as your method):
I'd use something like:
SET @QryBegining='SELECT fields JOINS etc etc without the where clause"
and then building another string given your "SearchValue" parameters
SET @WhereClause= 'WHERE FEIN = ' + @Criteria + ' OR ERN='+@nCriteria

And at the end execute it  with:
EXEC (@QryBegining+@WhereClause)

Perhaps might help you.
0
 

Author Comment

by:MiracleByDesign
ID: 22836921
I had your suggestion for readability in the beginning but since I am querying two separate tables it was a little too busy of a query and this must be able to be supported by junior level developers.  For some reason the only issue is with finding a match for the FEIN or ERN.  Thanks!
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 2

Expert Comment

by:TheLastStraw
ID: 22837562
If your data is actually numeric why don't you try

WHERE (FEIN = CAST(@Criteria AS Integer) ...

I asume your checkbox also equates to 1 as commonly  -1 '@SearchValue1 = -1 ?
0
 

Author Comment

by:MiracleByDesign
ID: 22845857
My data is numeric however there is a chance the data was saved with dashes or was imported from an outside source with dashes. Example FEIN = 11-111111 or 555-55-5555.  I have to allow for searching with or without dashes.  The @SearchValues just pass either a 1 or 0 to determine if the box has been checked on not on the search page.  The real problem is just in the where clause for FEIN or ERN.  Thanks for the suggestion.  If possible.  I will need to get this question resolved by Tuesday.  Thanks!
0
 

Author Comment

by:MiracleByDesign
ID: 22872925
Does anyone else have any ideas why this is not working.  I hate deleting questions but I really need assistance on this by the end of business on Tuesday.
0
 
LVL 1

Accepted Solution

by:
miketob earned 500 total points
ID: 22879466
Based on the suggestion that k_rasuri gave you, and that you serched for the FEIN values with a simpler query and didn't return the values you looked for, I assume that you're probably having "collation" issues, try this:

WHERE FEIN collate database_default = @Criteria collate database_default

and use it on every varchar field in your where clause
Hope it helps
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now