Stored Procedure with where clause

RIAS
RIAS used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David ToddSenior Database Administrator

Commented:
Hi,

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

Regards
  David

Author

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

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks,
Will try and brb

Author

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

Commented:
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

Author

Commented:
No, it brings all the records up. The filter does not seem to be working at all.
Yes, the cast will be corrected.

Commented:
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?

Author

Commented:
PrisCode, and ClientCode are identical in the two tables.

Author

Commented:
example:
PrisCode, and ClientCode :
PNJ001
UKI007
KLT056

Commented:
I have asked sql form, which is in the database, to simulate select in my local sql db

Author

Commented:
Any suggestion on the Stored Procedure.
Senior Developer
Commented:
Yup: start using the appropriate data types for your parameters.

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

Author

Commented:
Hi Ste5an,
Thanks and brb

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial