CREATE PROCEDURE [dbo].[sp_CAPI1_GetSurfaceQueryDataCountPartsMaster]
	@SurfaceID int,
	@UserLink int = 0,
	@Filter Varchar(MAX) = '',
	@Search Varchar(500)
AS
BEGIN
	
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

		DECLARE @query  AS NVARCHAR(MAX),
			@query2  AS VARCHAR(MAX) = ''
		
		set @query = N'SELECT Count(*) 
						FROM 	publ_PartsMaster publ 
						LEFT OUTER JOIN qt_PartsAlternates a ON a.ParentSurfaceItemId = publ.itemID 
						WHERE publ.isActive = 1 	' 
					declare @whereCols varchar(max) = ' PartsAlternates_PartsAlternates_AlternatePartNumber LIKE ''%' + @Search + '%'' '
		IF @Search <> ''
		BEGIN
			set @query2 += ' AND (   [PartsMaster_PartsMasterDetails_PartsMasterPartNumber] LIKE  ''%' + @Search + '%''  OR  [PartsMaster_PartsMasterDetails_PartsMasterCEPNumber] LIKE  ''%' + @Search + '%''  
				OR  [PartsMaster_PartsMasterDetails_PartsMasterDescription] LIKE ''%' + @Search + '%''  OR  [PartsMaster_PartsMasterDetails_PartsMasterAdditionalDescription] LIKE ''%' + @Search + '%''  
				OR  [PartsMaster_PartsMasterDetails_PartsMasterItemVetted] LIKE ''%' + @Search + '%''  
				OR [PartsAlternates_PartsAlternates_AlternatePartNumber] LIKE ''%' + @Search + '%'' )   '
		END

		IF (LEN(@Filter) > 0)
		BEGIN
			set @query2 += ' AND ' + @Filter															
		END
			
						
		exec (@query+@query2);
END




