Stored Procedure with where clause

Hello,
I have a stored procedure , which cannot search .
please find the attached SP.
Please suggest how can I modify it to get the searches/filter in it.

Thanks
EXAMPLE1.TXT
RIASAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:
Hi,

Can you clarify exactly which filter is not searching on, preferably with sample data and actual output vs desired results?

Regards
  David
RIASAuthor Commented:
Hi,
Filters like Ref, Invoicenumber.

IF @InvoiceDate1  <> '' AND @InvoiceDate2  <> ''
		     SET @SQL =@SQL+ '   AND [InvoiceDate] >= ' + ''''+ CAST(@InvoiceDate1  AS VARCHAR(20))  + '''' + ' and [InvoiceDate] <= ' + ''''+ CAST(@InvoiceDate2  AS VARCHAR(20)) +''''		  
	   ELSE IF  @InvoiceDate1  <> ''
			 SET @SQL = @SQL+ '   AND  [InvoiceDate] >= '+ '''' + CAST(@InvoiceDate1  AS VARCHAR(20))  +''''	
	   Else IF @InvoiceDate2  <> ''		
		     SET @SQL = @SQL+ '  AND  [InvoiceDate] <= '+'''' + CAST(@InvoiceDate2  AS VARCHAR(20))+''''			 	 
       IF  @SupplierTitle  <> ''
	         SET @SQL = @SQL + '  AND [SupplierTitle]   LIKE  ''%' +  CAST(@SupplierTitle AS VARCHAR(100))  +  '%'''      
       IF  @ClientCode   <> ''
              SET @SQL = @SQL + '  AND ClientCode = ''' +  CAST(@ClientCode AS NVARCHAR(10))+ ''''
	   IF  @Checked <> '0'  or @Checked   <> null
             SET @SQL = @SQL + '  AND Checked = ''' +  CAST(@Checked AS NVARCHAR(10))+ ''''
       IF  @Currency <> ''
            SET @SQL = @SQL + '  AND Currency = ''' +  CAST(@Currency AS NVARCHAR(12))+ '''' 
       IF  @GrossValue <> ''
            SET @SQL = @SQL + '  AND GrossValue = ''' +  CAST(@GrossValue AS NVARCHAR(255))+ ''''
	   IF  @Description1  <> ''
            SET @SQL = @SQL + '  AND SupplierInvoiceNo LIKE  ''%' + CAST(@Description1 AS  Nvarchar(MAX)) +  '%'''  
	   IF  @Description2  <> ''
            SET @SQL = @SQL + '  AND Description2 LIKE  ''%' + CAST(@Description2 AS  Nvarchar(MAX)) +  '%'''  	  
	   IF  @Comments <> ''
             SET @SQL = @SQL + '  AND Comments LIKE  ''%' + CAST(@Comments AS  Nvarchar(MAX)) +  '%''' 	 	 
	   IF  @GuestDetails <> ''
             SET @SQL = @SQL + '  AND GuestDetails LIKE  ''%' + CAST(@GuestDetails AS  Nvarchar(MAX)) +  '%''' 	 
	   IF  @InvoiceNumber <> ''
             SET @SQL = @SQL + '  AND InvoiceNumber  LIKE  ''%' +   CAST(@InvoiceNumber AS  Nvarchar(255)) +  '%'''   
	   IF  @CheckedBy <> ''
            SET @SQL = @SQL + '  AND CheckedBy = ''' +  CAST(@CheckedBy AS NVARCHAR(100))+ ''''		
	   IF  @InvRef <> ''
            SET @SQL = @SQL + '  AND Ref = ''' +  CAST(@InvRef AS NVARCHAR(50))+ ''''
	   IF  @InvAuth <> ''
            SET @SQL = @SQL + '  AND Auth = ''' +  CAST(@InvAuth AS NVARCHAR(50))+ ''''		
	   IF  @Note   <> ''
            SET @SQL = @SQL + '  AND Note = ''' +  CAST(@Note AS NVARCHAR(50))+ ''''
	   IF  @Position <> ''
            SET @SQL = @SQL + '  AND Position = ''' +  CAST(@Position AS NVARCHAR(10))+ ''''     
	   IF  @Originator <> ''
            SET @SQL = @SQL + '  AND  Originator = ''' +  CAST(@Originator AS NVARCHAR(100))+ '''' 	
  	  	

Open in new window


Thanks
Nikoloz KhelashviliSoftware DeveloperCommented:
Hi, can you remove all excess filters and verify it works for @InvoiceNumber parameter?

ALTER PROCEDURE  [dbo].[M_Select_PrsInv]

     
	   
    @Originator nvarchar(100) = NULL,	@InvoiceDate1 nvarchar(20)= NULL,@InvoiceDate2 nvarchar(20)= NULL,	@SupplierTitle nvarchar(255) = NULL,
	@ClientCode nvarchar (10) = NULL,@PRisCode nvarchar (50) = NULL,@InvAuth nvarchar(50)= NULL,
	@Currency nvarchar(12)=  NULL,	@GrossValue nvarchar(MAX)= NULL,	@Description1 nvarchar(MAX)= NULL,	
	@Description2 nvarchar(MAX)= NULL,@Comments nvarchar(MAX)= NULL,@GuestDetails nvarchar(MAX)= NULL,
	@InvoiceNumber nvarchar(255)= NULL,@Checked nvarchar(10)= NULL ,@Note nvarchar(20)= NULL,@CheckedBy nvarchar(100)= NULL,@Position nvarchar(10)= NULL,
	@CompanyRecharge nvarchar(30)= NULL,@Quantity nvarchar(255)= NULL,@Netvalue nvarchar(255)= NULL,@InvRef nvarchar(50)= NULL
	


         
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  
	
    DECLARE @SQL NVARCHAR(MAX)
 	
	SET @SQL  = 'SELECT      [ClientCode] as [Pris Code],     STUFF((SELECT '','' + tt1.ref  
        FROM CLIENT_REFERENCE tt1       
		     WHERE tt2.ClientCode = tt1.PrisCode     
			        ORDER BY Ref        
					    FOR XML PATH('''')), 1,1, '''')[Ref] , 
	 STUFF((SELECT '','' + tt1.OrgAuth          
	 FROM CLIENT_REFERENCE tt1 WHERE tt2.ClientCode = tt1.PrisCode'
	 
  	
	   IF  @InvoiceNumber <> ''
           BEGIN
             SET @SQL = @SQL + '  AND InvoiceNumber  LIKE  ''%' +   CAST(@InvoiceNumber AS  Nvarchar(255)) +  '%'''   
	        SET @SQL = @SQL +' ORDER BY Ref  FOR XML PATH('''')), 1, 1, '''')[auth],  
	 Checked, LineChecked, CorrNum as [CorrespondenceNumber], SupplierInvoiceNo ,  
	 InvoiceNumber,GrossValue,Position,[InvoiceDate], SupplierTitle,ClientCode, PrisCode,Currency,
	 [Description],Description1,Description2,GuestDetails as PatientDetails ,Checkedby,Note,CompanyRecharge,Quantity,Netvalue,
	 Originator,InvoiceitemGUID,InvoiceGUID 
   FROM PREXTMED tt2
   GROUP BY [clientCode], Checked, LineChecked, CorrNum , SupplierInvoiceNo,InvoiceNumber,GrossValue,Position,
   [InvoiceDate],SupplierTitle,ClientCode, PrisCode,Currency,   [Description],Description1,Description2, 
   GuestDetails ,Checkedby,Note,CompanyRecharge,Quantity, Netvalue,Originator,InvoiceitemGUID,InvoiceGUID,
   tt2.ref,tt2.auth'
	
	        SET @SQL = @SQL  + ' ORDER BY  InvoiceDate DESC, SupplierInvoiceNo, Position'
	 END
	  
       EXECUTE (@SQL)
	   INSERT INTO Ml_DEBUG(SQL) VALUES (@SQL)
	   
	  --Print @SQL
	 
	
END

Open in new window

Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

RIASAuthor Commented:
Thanks,
Will try and brb
RIASAuthor Commented:
This query looks like this when I give a filter of invoice number
SELECT      [ClientCode] as [Proactis Code],     STUFF((SELECT ',' + tt1.ref            FROM CLIENT_REFERENCE tt1                WHERE tt2.ClientCode = tt1.ProisCode   ORDER BY Ref                   FOR XML PATH('')), 1,1, '')[Ref] ,     STUFF((SELECT ',' + tt1.OrgAuth              FROM CLIENT_REFERENCE tt1 WHERE tt2.ClientCode = tt1.ProisCode and  InvoiceNumber  ='P87'   ORDER BY Ref  FOR XML PATH('')), 1, 1, '')[auth],      Checked, LineChecked, CorrNum as [CorrespondenceNumber], SupplierInvoiceNo ,      InvoiceNumber,GrossValue,Position,[InvoiceDate], SupplierTitle,ClientCode, ProisCode,Currency,    [Description],Description1,Description2,GuestDetails as PatientDetails ,Checkedby,Note,CompanyRecharge,Quantity,Netvalue,    Originator,InvoiceitemGUID,InvoiceGUID      FROM PREXTMED tt2     GROUP BY [clientCode], Checked, LineChecked, CorrNum , SupplierInvoiceNo,InvoiceNumber,GrossValue,Position,     [InvoiceDate],SupplierTitle,ClientCode, ProisCode,Currency,   [Description],Description1,Description2,      GuestDetails ,Checkedby,Note,CompanyRecharge,Quantity, Netvalue,Originator,InvoiceitemGUID,InvoiceGUID,     tt2.ref,tt2.auth ORDER BY  InvoiceDate DESC, SupplierInvoiceNo, Position

Open in new window

Nikoloz KhelashviliSoftware DeveloperCommented:
This query looks like this when I give a filter of invoice number

Does it give correct resultset, when executing?

I also suggest trimming nvarchar type parameters, when composing sql statement where clause and check if it will work as expected. By the way, why are you casting nvarchar type parameter to nvarchar?


@InvoiceNumber nvarchar(255)= NULL

Open in new window


CAST(@InvoiceNumber AS  Nvarchar(255))

Open in new window

RIASAuthor Commented:
No, it brings all the records up. The filter does not seem to be working at all.
Yes, the cast will be corrected.
Nikoloz KhelashviliSoftware DeveloperCommented:
Can you post one or two example from your xml records to test on it?

And how are two columns related to each other, PrisCode, and ClientCode?
RIASAuthor Commented:
PrisCode, and ClientCode are identical in the two tables.
RIASAuthor Commented:
example:
PrisCode, and ClientCode :
PNJ001
UKI007
KLT056
Nikoloz KhelashviliSoftware DeveloperCommented:
I have asked sql form, which is in the database, to simulate select in my local sql db
RIASAuthor Commented:
Any suggestion on the Stored Procedure.
ste5anSenior DeveloperCommented:
Yup: start using the appropriate data types for your parameters.

Then: consider using OPTION (RECOMPILE) with a catch-all instead of dynamic SQL.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Hi Ste5an,
Thanks and brb
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.