Solved

SQL Search Query not returning expected results

Posted on 2008-10-29
8
233 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

747 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

10 Experts available now in Live!

Get 1:1 Help Now