SQL Stored Procedure Issue - Search Stored Procedure

This is the Stored Procedure below -> 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BPI_SearchArchivedBatches]
GO


/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/

/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/2/2007 4:52:19 PM ******/

 

CREATE  PROCEDURE BPI_SearchArchivedBatches
(
 @V_BatchStatus Varchar(30)= NULL,
 @V_BatchType VARCHAR(50) = NULL,
 @V_BatchID NUMERIC(9) = NULL,
 @V_UserID CHAR(8) = NULL,
 @V_FromDateTime DATETIME = '01/01/1900',
 @V_ToDateTime DATETIME = '01/01/3000',
 @SSS varchar(500) = null,
 @i_WildCardFlag INT
)

AS

DECLARE @SQLString NVARCHAR(4000)
DECLARE @ParmDefinition NVARCHAR (4000)

 

IF (@i_WildCardFlag=0)
BEGIN

 SET @SQLString='SELECT    
  Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,
  Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,
  Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,
  Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,
  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,
  BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType
 
 FROM        
  Batch
 INNER JOIN
   BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID
 WHERE
  ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID ))
 AND
  ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID ))
 AND
  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <=  @V_ToDateTime ))
 AND
  Batch.Archived_Status = 1 '

 if (@V_BatchStatus IS not null)
 begin
  set @SQLString=@SQLString + ' AND
  (Batch.Status_Code in ('+@V_BatchStatus+'))'
 end

 if (@V_BatchType IS not null)
 begin
  set @SQLString=@SQLString + ' AND
  (BatchTemplate.Batch_Type  in ('+@V_BatchType+'))'
 end
 
END

ELSE
BEGIN
 SET @SQLString='SELECT    
  Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,
  Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,
  Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,
  Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,
  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,
  BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType
 
 FROM        
  Batch
 INNER JOIN
  BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID
 WHERE
  ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS ))
 AND
  ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID ))
 AND
  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <=  @V_ToDateTime ))
 AND
  Batch.Archived_Status = 1 '

 if (@V_BatchStatus IS not null)
 begin
  set @SQLString=@SQLString + ' AND
  (Batch.Status_Code in ('+@V_BatchStatus+'))'
 end

 if (@V_BatchType IS not null)
 begin
  set @SQLString=@SQLString + ' AND
  (BatchTemplate.Batch_Type  in ('+@V_BatchType+'))'
 end

END

PRINT @SQLString

SET @ParmDefinition = N'
 @V_BatchStatus Varchar(30),
 @V_BatchType VARCHAR(50),
 @V_BatchID NUMERIC(9),
 @V_UserID CHAR(8),
 @V_FromDateTime DATETIME ,
 @V_ToDateTime DATETIME,
 @SSS varchar(500)'

EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType ,
 @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


 

 

The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.

REQUIREMENT:

The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.

 

Please help me regarding the same.

 

Thanks in advance.

 

Sandeep Kumar