[Solved] Stored Procedure where clause is not filtering records based on condition in SQL Server

lingers
 
on Sep 20, 2021 01:12 AM
1152 Views

I have this grid view page with a button with id ID="Inkview" and OnClick="Inkview_Click" that was working well till i added jquery search and jquery page size dropdown list.

the procedure is not working well. What i mean is that in the data only 5 records has their active status as ='1' and the other five(5) have their active status ='0' yet on the gridview have 10 records including records whose status ='0' which is no suppose to be so

from the procedure you can see that the record should only display records whose status is s.active='1', but on the gridviw in the snap shot you posted sir it has both status whose s.active='1' and s.active='0' that makes is 10 instead of 5

CREATE PROCEDURE [dbo].[cylin1200]
    @SearchTerm VARCHAR(100) = '',
    @PageIndex INT = 1,
    @PageSize INT = 20,
    @RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
    SELECT DISTINCT s.id, DENSE_RANK() OVER (ORDER BY s.[id] DESC) AS RowNumber,
    c.[circumference],c.[colour],c.[posino],s.[no],s.[totalrev],o.[deliverydate],o.[purchaseorderno],o.[supplier],o.[waybillno],o.[comment],j.[pid],j.[description]
    INTO #Results
    from [job_cylinder] c 
    inner join stock s  on s.typeid = c.id
    inner join  orders o   on   o.orderid = s.orderid 
    inner join job j  on j.id= c.pid  
    where  o.producttype='3' and s.active='1'
    AND (c.[circumference] LIKE @SearchTerm + '%' OR @SearchTerm = '') 
    OR (c.[colour] LIKE @SearchTerm + '%' OR @SearchTerm = '')
    OR (c.[posino] LIKE @SearchTerm + '%' OR @SearchTerm = '')
    OR (s.[no] LIKE @SearchTerm + '%' OR @SearchTerm = '')
    OR (s.[totalrev] LIKE @SearchTerm + '%' OR @SearchTerm = '')


    OR (c.id LIKE @SearchTerm + '%' OR @SearchTerm = '')
    OR (j.[pid] LIKE @SearchTerm + '%' OR @SearchTerm = '')
    OR (c.[posino] LIKE @SearchTerm + '%' OR @SearchTerm = '')
    OR (j.[description] LIKE @SearchTerm + '%' OR @SearchTerm = '')
	OR (o.[purchaseorderno] LIKE @SearchTerm + '%' OR @SearchTerm = '')
    OR (o.[deliverydate] LIKE @SearchTerm + '%' OR @SearchTerm = '')
    OR (o.[supplier] LIKE @SearchTerm + '%' OR @SearchTerm = '')
    OR (o.[waybillno] LIKE @SearchTerm + '%' OR @SearchTerm = '')
    OR (o.[comment] LIKE @SearchTerm + '%' OR @SearchTerm = '')
    
    SELECT @RecordCount = Count(*) FROM #Results
   
    SELECT * FROM #Results WHERE
    RowNumber BETWEEN  (@PageIndex-1)*@PageSize + 1 AND (((@PageIndex-1)*@PageSize + 1)+@PageSize)-1 OR @PageIndex = -1
   
    DROP TABLE #Results
END
GO

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 24, 2021 12:33 AM

Hi lingers,

Put the Like condition ser searching all column inside a bracket.

Refer below modified stored procedure.

ALTER PROCEDURE [dbo].[cylin12002]
    @SearchTerm VARCHAR(100) = '',
    @PageIndex INT = 1,
    @PageSize INT = 20,
    @RecordCount INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	SELECT DISTINCT s.id, DENSE_RANK() OVER (ORDER BY s.[id] DESC) AS RowNumber,
	c.[circumference],c.[colour],c.[posino],s.[no],s.[totalrev],o.[deliverydate],o.[purchaseorderno],o.[supplier],o.[waybillno],o.[comment],j.[pid],j.[description],s.active,o.producttype
	INTO #Results
	from [job_cylinder] c 
	inner join stock s  on s.typeid = c.id
	inner join  orders o   on   o.orderid = s.orderid 
	inner join job j  on j.id= c.pid 
	where (o.producttype='3' and s.active='1') 
	AND (
		(c.[circumference] LIKE @SearchTerm + '%' OR @SearchTerm = '') 
		OR (c.[colour] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (c.[posino] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (s.[no] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (s.[totalrev] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (c.id LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (j.[pid] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (c.[posino] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (j.[description] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (o.[purchaseorderno] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (o.[deliverydate] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (o.[supplier] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (o.[waybillno] LIKE @SearchTerm + '%' OR @SearchTerm = '')
		OR (o.[comment] LIKE @SearchTerm + '%' OR @SearchTerm = '')
	)	

    SELECT @RecordCount = Count(*) FROM #Results
   
    SELECT * FROM #Results WHERE
    RowNumber BETWEEN  (@PageIndex-1)*@PageSize + 1 AND (((@PageIndex-1)*@PageSize + 1)+@PageSize)-1 OR @PageIndex = -1
   
    DROP TABLE #Results
END