Search query on multiple columns of a table?

Question

we implement search on a table like this

   create proc prcSearchMember 
(
@UserId uniqueidentifier,
@MemberFirstName varchar(20),
@MemberMiddleName varchar(20),
@MemberLastName varchar(20),
@FamilyHeadName varchar(50),
@FatherName varchar(50),
@MotherName varchar(50),
@DOB datetime,
@GotraID int,
@SectID int,
@BloodGroupID int,
@EducationLevelID int, 
@EducationFieldID int,
@HouseNumber varchar(20),
@StreetName varchar(50),
@Area varchar(50),
@LandMark varchar(50),
@StateID int,
@CountryID int,
@CityID int, 
@PhoneNumber varchar(15),
@EmailAddress varchar(50),
@MaritalStatus varchar(20),
@OccupationTypeID int,
@Gender varchar(10),
@IsSubmit bit
)
as 
if(@MemberFirstName is not null )
 Begin  
Select MemberFirstName,UserId from  MemberInfo
 where MemberFirstName  like  @MemberFirstName
 end
else if (@MemberMiddleName is not null )
Begin  
Select MemberFirstName,UserId from  MemberInfo 
where MemberMiddleName like @MemberMiddleName
end
else if (@MemberMiddleName is not null ) 
Begin    
Select MemberFirstName,UserId from  MemberInfo   where   MemberLastName  like @MemberLastName
end
else if (@MemberLastName is not null ) 
Begin   
Select MemberFirstName,UserId from   MemberInfo   where FamilyHeadName  like @MemberLastName  
end
else if (@MemberLastName is not null ) 
Begin   
Select MemberFirstName,UserId from   MemberInfo   where  FatherName  like @FatherName 
end
else if (@MemberLastName is not null ) 
Begin   
Select MemberFirstName,UserId from   MemberInfo   where MotherName  like @MotherName 
end
else if (@DOB is not null ) Begin   
Select MemberFirstName,UserId from   MemberInfo   where DOB like @DOB
end
else if (@GotraID is not null ) 
Begin    
Select MemberFirstName,UserId from   MemberInfo   where  GotraID like @GotraID
end
else if (@SectID is not null ) Begin    
Select MemberFirstName,UserId from   MemberInfo   where SectID=@SectID
end
else if (@BloodGroupID is not null ) Begin    
Select MemberFirstName,UserId from   MemberInfo   where BloodGroupID like @BloodGroupID  
end
else if (@EducationLevelID is not null ) Begin    
Select MemberFirstName,UserId from   MemberInfo   where EducationLevelID=@EducationLevelID 
end
else if (@EducationFieldID is not null ) Begin    
Select  MemberFirstName,UserId from   MemberInfo   where EducationFieldID like EducationFieldID
end
else if (@HouseNumber is not null ) Begin    
Select MemberFirstName,UserId from   MemberInfo   where HouseNumber like @HouseNumber  
end
else if (@StreetName is not null ) Begin    
Select MemberFirstName,UserId from   MemberInfo   where StreetName like @StreetName  
end
else if (@Area is not null ) Begin    
Select MemberFirstName,UserId from   MemberInfo   where Area like @Area  
end
else if (@LandMark is not null ) Begin    
Select MemberFirstName,UserId from   MemberInfo   where LandMark like @LandMark  
end
else if (@CountryID is not null ) Begin    
Select MemberFirstName,UserId from MemberInfo where CountryID like @CountryID
end
else if (@StateID is not null ) 
Begin  
select MemberFirstName,UserId from   MemberInfo   where StateID like @StateID 
end
else if (@CityID is not null ) Begin    
Select MemberFirstName,UserId from   MemberInfo   where CityID like CityID 
end
else if (@OccupationTypeID is not null ) Begin    
Select  MemberFirstName,UserId from   MemberInfo   where  OccupationTypeID like @OccupationTypeID 
end
else if (@PhoneNumber is not null ) Begin    
Select  MemberFirstName,UserId from   MemberInfo   where  PhoneNumber like @PhoneNumber 
end
else if (@EmailAddress is not null ) 
Begin     
Select MemberFirstName,UserId from   MemberInfo   where EmailAddress  like @EmailAddress 
 end
else if (@MaritalStatus is not null )
 Begin     
Select MemberFirstName,UserId from   MemberInfo   where MaritalStatus  like @MaritalStatus 
 end
else if (@Gender is not null ) 
Begin     
Select MemberFirstName,UserId from  MemberInfo   where Gender like @Gender 
end
else if (@UserId is not null ) Begin    
Select MemberFirstName,UserId from  MemberInfo   where  UserId like @UserId

these all are the columns of single table

so if there is any method to implement search query like this in this way

foreach (column_name in INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName')
   select primary_key_coumn 
   from table_name 
   where column_name like @column_name

where @column_name is the value of particular column

Answer 1

You can let you condition fall back to tautology.
Condition that are tautology are never evaluated so something like

SELECT MemberFirstName
     , UserId 
FROM   MemberInfo
WHERE  MemberFirstName like MemberFirstName

is equivalent to

SELECT MemberFirstName
     , UserId 
FROM   MemberInfo

it is possible to change the query in a way that the condition will become a tautology when a parameter is NULL using COALESCE or ISNULL.

For the first few condition it is something like

SELECT MemberFirstName, UserId 
FROM   MemberInfo
WHERE  MemberFirstName  like COALESCE(@MemberFirstName, MemberFirstName)
  AND  MemberMiddleName like COALESCE(@MemberMiddleName, MemberMiddleName)
  AND  MemberLastName   like COALESCE(@MemberLastName, MemberLastName)
  AND  FamilyHeadName   like COALESCE(@FamilyHeadName, FamilyHeadName)

Answer 2

Try the following,

DECLARE @ColName varchar(126) = 'col_name'
DECLARE @ColVal varchar(64) = 'col_val'

DECLARE @SQL AS nvarchar(MAX)
SET @SQL = ''

SELECT @SQL = @SQL + 'SELECT * FROM ' + IST.TABLE_SCHEMA + '.' + IST.TABLE_NAME 
                       +' where '+ @ColName +' = '''+ @ColVal +''' ' + CHAR(13)
FROM 
    INFORMATION_SCHEMA.TABLES IST
    INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC ON IST.TABLE_NAME = ISC.TABLE_NAME
WHERE
    ISC.COLUMN_NAME = @ColName

EXEC (@SQL)

Answer 3

I'm not that sure what it is you're trying to achieve. Are you asking "Is there a beter way fo writing this search?". If so, then yes, absolutely. As far as I can see each IF branch selects the same columns so if you make sure all your stored proc arguments are defaulted to NULL in the declaration then you can do the following:

SELECT MemberFirstName, UserId 
FROM  MemberInfo
WHERE (MemberFirstName  LIKE @MemberFirstName OR @MemberFirstName IS NULL)
AND (MemberMiddleName LIKE @MemberMiddleName OR @MemberMiddleName IS NULL)
AND (MemberLastName  LIKE @MemberLastName OR @MemberLastName  IS NULL)
AND --You get the picture by now. Keep adding your conditions here.

The optimizer is intelligent enough to not bother with any condition which evaluates to NULL (which is why you must make sure the arguments are defaulted to NULL).

Note that a number of your branches will never be executed because you have:

else if (@MemberLastName is not null ) 
Begin   
Select MemberFirstName,UserId from   MemberInfo   where FamilyHeadName  like @MemberLastName  
end
else if (@MemberLastName is not null ) 
Begin   
Select MemberFirstName,UserId from   MemberInfo   where  FatherName  like @FatherName 
end
else if (@MemberLastName is not null ) 
Begin   
Select MemberFirstName,UserId from   MemberInfo   where MotherName  like @MotherName 
end

Only the first of those branches will ever be executed.

I'm not sure why you're asking about primary key columns and why you want to iterate the columns in a table to get the primary key column? Within a single table the primary key column(s) for that table will be the same for every column name in that table you examine. What difference will that make to you? What is the actual problem you are trying to solve? It is also worth pointing out that since SQL Server 2005 it is recommended that you use the system catalog views and not INFORMATION_SCHEMA, and they're pretty simple to use.

Answer 4

You can build a dynamic SQL dependent on your search Parameter.

See example 2.0 in the following link: Building Dynamic SQL in Stored Procedure