Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Search Query not returning expected results

Posted on 2008-10-29
8
Medium Priority
?
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 

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
 
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

660 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