asked on

SQL Search Query not returning expected results

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

k_rasuri

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)

MiracleByDesign

ASKER

Yes, I did replace the FEIN with the @Criteria and I still could not pull up the results I needed. Thanks for the suggestion!

miketob

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.

MiracleByDesign

ASKER

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!

TheLastStraw

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 ?

MiracleByDesign

ASKER

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!

MiracleByDesign

ASKER

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.

ASKER CERTIFIED SOLUTION

miketob

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial