SQL Server - how to perform a search on a concatenated field

Try...Also can you post the entire dynamic query ..

--

 SELECT Project.ProjectNumber,
			        Project.ApplicantName,
			        Project.SpecificLocation,	
			        ProjectAssignmentInformation.[Description],
			        STUFF(( SELECT ', ' + Employee.FirstName + ' ' + Employee.LastName
							 FROM Employee
								  INNER JOIN ProjectEmployee
									ON Employee.EmployeeId = ProjectEmployee.EmployeeId 
							WHERE ProjectEmployee.ProjectId = Project.ProjectId
						      FOR XML PATH (''), Type).value('.', 'nvarchar(max)'), 1, 2, '') AS ProjectManagers,
			        ProjectType.[Type] AS ProjectType	     
			        
			   FROM Project	
				    LEFT JOIN ProjectType
				       ON ProjectType.ProjectTypeId = Project.ProjectTypeId				    
				    LEFT JOIN ProjectAssignmentInformation	   
				 	   ON Project.ProjectId = ProjectAssignmentInformation.ProjectId	
			  WHERE ProjectAssignmentInformation.[Description]  like +'%'+ @Search +'%' OR
			        Project.ProjectNumber  like + '%'+ @Search +'%' OR
			        Project.SpecificLocation  like +'%'+ @Search +'%' OR
			        ProjectType.[Type]  like +'%'+ @Search +'%' OR
			        ProjectManagers  like +'%'+ @Search +'%'			    			   
		  ORDER BY Project.ProjectNumber

--

Select allOpen in new window

thanks for your reply. This one wont work. already tried. there is no dynamic SQL. this is a aselect stmt from my stored proc

Could you please post search string and tables and their data so that we can have a look?

Data: Details.xlsx
this is how I invoke my Stored Proc.

USE [StateRevolvingFund]
GO

DECLARE	@return_value int,
		@Success bit,
		@Message varchar(1000)

EXEC	@return_value = [dbo].[usp_GetForHomePageSearch]
		@Search = 'b',
		@Success = @Success OUTPUT,
		@Message = @Message OUTPUT

SELECT	@Success as N'@Success',
		@Message as N'@Message'

SELECT	'Return Value' = @return_value

GO

Select allOpen in new window

Full Stored Proc

USE [StateRevolvingFund]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===================================
-- Author:		Subbu
-- Create date: 10/31/2016
-- Description:	Retrieve records for HomePage Search
-- ===================================
ALTER PROCEDURE [dbo].[usp_GetForHomePageSearch]   
    @Search  varchar(50),   
	@Success bit output,
	@Message varchar(1000) output	 
AS
BEGIN
	BEGIN TRY  
	       BEGIN 	                     
		     SELECT Project.ProjectNumber,
			        Project.ApplicantName,
			        Project.SpecificLocation,	
			        ProjectAssignmentInformation.[Description],			     
			        STUFF((SELECT ', ' + Employee.FirstName + ' ' + Employee.LastName
							 FROM Employee
								  INNER JOIN ProjectEmployee
									      ON Employee.EmployeeId = ProjectEmployee.EmployeeId 
							WHERE ProjectEmployee.ProjectId = Project.ProjectId
						    FOR XML PATH (''), Type).value('.', 'nvarchar(max)'), 1, 2, '') AS ProjectManagers,
			        ProjectType.[Type] AS ProjectType	
			   FROM Project	
				    LEFT JOIN ProjectType
				           ON ProjectType.ProjectTypeId = Project.ProjectTypeId				    
				    LEFT JOIN ProjectAssignmentInformation	   
				 	       ON Project.ProjectId = ProjectAssignmentInformation.ProjectId	
				 	
			  WHERE Project.ProjectNumber  like +'%'+ @Search +'%' OR
			        Project.ApplicantName  like +'%'+ @Search +'%' OR
			        Project.SpecificLocation  like +'%'+ @Search +'%' OR
			        ProjectAssignmentInformation.[Description]  like +'%'+ @Search +'%' OR
			        ProjectType.[Type]  like +'%'+ @Search +'%' 			     
		   ORDER BY Project.ProjectNumber            
             SELECT @Success = 1,
					@Message = 'Successfully retrieved records for Home Page Search Results'
	      END	
	END TRY
	BEGIN CATCH
		  SELECT @Success = 0,
			     @Message = 'An error occurred retrieving Home Page Search Results, the error was:' + ERROR_MESSAGE()
	END CATCH
END

Select allOpen in new window

Below is working for me.

First remove all filters

then

I think you should 1 filter at a time and then increase the filters and check. that way you can easily make it.

--

CREATE TABLE Proj
(
	 ProjectNumber VARCHAR(100)	
	,ApplicantName	VARCHAR(100)
	,SpecificLocation VARCHAR(100)	 
	,Description	VARCHAR(100)
	,ProjectManagers	VARCHAR(100)
	,ProjectType VARCHAR(100)
)
GO

INSERT INTO Proj VALUES
('1002-01',	'lansing city',	'ew', 	'Pay your bills son',	'Sue Casler, Michael Grenzicki',	'SAW Grant' ),
('1010-01',	'SAW Grant for Julia',	'5th floor Hollister Building',	NULL	,NULL,	'SAW Grant' ),
('1011-01',	'SAW Grant for Julia',	'5th floor Hollister Building',	NULL	,NULL,	'SAW Grant' )

GO

DECLARE @Search AS VARCHAR(100) = 'S'

SELECT * FROM Proj

WHERE 

ApplicantName LIKE '%' + @Search + '%' OR 
Description LIKE '%' + @Search + '%' OR 
ProjectManagers LIKE '%' + @Search + '%' OR 
ProjectType LIKE '%' + @Search + '%'




--

Select allOpen in new window

ProjectNumber      ApplicantName      SpecificLocation      Description      ProjectManagers      ProjectType
1002-01      lansing city      ew      Pay your bills son      Sue Casler, Michael Grenzicki      SAW Grant
1010-01      SAW Grant for Julia      5th floor Hollister Building      NULL      NULL      SAW Grant
1011-01      SAW Grant for Julia      5th floor Hollister Building      NULL      NULL      SAW Grant

Pawan, thanks for your response. Project manager is not a single column in the table.  If you take a closer look in to my stored proc how the project manager is formed by concatenation of two names and it is derived.  It is not a straightforward DB column.

 STUFF((SELECT ', ' + Employee.FirstName + ' ' + Employee.LastName
							 FROM Employee
								  INNER JOIN ProjectEmployee
									      ON Employee.EmployeeId = ProjectEmployee.EmployeeId 
							WHERE ProjectEmployee.ProjectId = Project.ProjectId
						    FOR XML PATH (''), Type).value('.', 'nvarchar(max)'), 1, 2, '') AS ProjectManagers,

Select allOpen in new window