none
Filter rows based on Where condition AND-OR ?

    Question

  • Hi,

    I have table "Table1" and columns are

    Id

    AddendumNo

     

    ClaimNo

     

    Description

    1

    A1

    C1

    D1

    2

    A1

    C1

    D2

    3

    A1

    C2

    D1

    4

    A2

    C3

    D3

    5

    A2

    C1

    D2

    I have three controls in the UI.

    If I give txtA1.Text =”A1” only then should be return Id- 1,2,3 records.

    If I give txtC1.Text =”C1” only then should be return Id- 1,2,5 records.

    If I give txtA1.Text =”A1” AND txtC1.Text =”C1” then should be return Id- 1,2 records.

    If I give give txtA1.Text =”A1” AND txtC1.Text =”C1” AND txtD1.Text=”D1” then should be return Id-1 record.

    Please help me to write dynamic sql query ?

    Thank you.


    • Edited by Narsa Tuesday, February 25, 2014 7:39 AM
    Tuesday, February 25, 2014 7:33 AM

Answers

  • something like below

    WHERE (AddendumNo = @A1 OR @A1='')
    AND (ClaimNo = @C1 OR @C1 = '')
    AND Description = @D1 OR @D1 = '')

    and in application set default value of textboxes as blank.

    No need of dynamic sql


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Narsa Tuesday, February 25, 2014 9:08 AM
    Tuesday, February 25, 2014 8:11 AM
  • Try the below and show us the Print text value: (I could not test it)

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		<Reddy>
    -- Create date: <17/02/2014>
    -- Description:	<GET BS Attachments>
    -- =============================================
    ALTER PROCEDURE [dbo].[GetBSAttachments] @StartRowIndex AS  VARCHAR(10)
    	,@MaximumRows AS  VARCHAR(10)
    	,@AddendumNo AS NVARCHAR(254) = NULL
    	,@ClaimNo AS NVARCHAR(254) = NULL 
    	,@Description AS NVARCHAR(3000) = NULL	 
    	,@fileType AS VARCHAR(12)
    AS
    BEGIN		
    
    	DECLARE @sqlColumns AS VARCHAR(8000) = ''
    	 DECLARE @sqlCond AS VARCHAR(8000) = ''
    	DECLARE @sqlCond1 AS VARCHAR(8000) = ''
    	DECLARE @sqlLimit AS VARCHAR(500) = ''
    	 DECLARE @sqlCount AS VARCHAR(500) = ''
    
    	Set @AddendumNo = (Select Case when LEN(LTRIM(RTRIM(@AddendumNo))) = 0 Then NULL Else @AddendumNo End)
    	Set @Claimno = (Select Case when LEN(LTRIM(RTRIM(@Claimno))) = 0 Then NULL Else @Claimno End)
    	Set @Description = (Select Case when LEN(LTRIM(RTRIM(@Description))) = 0 Then NULL Else @Description End)
    	
    	
    	IF (@fileType = 'Attach')
    	 BEGIN
    		SET @sqlCond1  = 'AND FileExtension <> ''msg'''
    	END
    	 ELSE IF(@fileType = 'Email')
    	BEGIN
    		SET @sqlCond1  = 'AND FileExtension = ''msg'''
    	END
    
    	SET @sqlColumns = 'SELECT   * FROM
    	(Select id,AddendumNo,Description,ClaimNo , ROW_NUMBER() OVER (ORDER BY id) AS RowRank '
    	SET @sqlCount = 'Select COUNT(*) AS BSAttachTotalCount'
    	SET @sqlCond = ' FROM EFMS.dbo.Table1 WHERE FileExtension IS NOT NULL ' + @sqlCond1 + '
    
    	-- Have to modify this logic
    	AND (AddendumNo=  ISNULL(''' + @AddendumNo + ''',AddendumNo))
    	AND (Claimno=  ISNULL(''' + @Claimno + ''',Claimno))
    	AND ((''' + @Description + ''' IS NOT NULL AND Description like ''%' + @Description + '%'')  OR (''' + @Description + ''' is null and Description=Description''))'
     
    	SET @sqlLimit = ' ) AS BSAttachmentsWithRowNumbers WHERE RowRank BETWEEN ''' + CONVERT(NVARCHAR(10), @StartRowIndex) + ''' AND ''' + CONVERT(NVARCHAR(10), @MaximumRows) + ''''
    
    
    	Print @sqlColumns + @sqlCond + @sqlLimit -- To show in the grid by page size 	 
    	Print 'Another query'
    	Print (@sqlCount + @sqlCond) -- To get total records count with selection criteria.
    		 
    
    
    
    END

    • Marked as answer by Narsa Tuesday, February 25, 2014 9:08 AM
    Tuesday, February 25, 2014 8:25 AM

All replies

  • Why do you want Dynamic sql?

    Try the below:

    create proc sp_test(@Addnedum int = NULL,@Claimno int = NULL,@Desc int=null)
    as
    Begin
    	Set @Addnedum = (Select Case when LEN(LTRIM(RTRIM(@Addnedum)) = 0 Then NULL Else @Addnedum End)
    	Set @Claimno = (Select Case when LEN(LTRIM(RTRIM(@Claimno)) = 0 Then NULL Else @Claimno End)
    	Set @Desc = (Select Case when LEN(LTRIM(RTRIM(@Desc)) = 0 Then NULL Else @Desc End)
    	
    	Select * From Tablename where
    	AddendumNo = ISNULL(@A1,AddendumNo) and 
    	ClaimNo = ISNULL(@B1,Claimno) and
    	Description = ISNULL(@C1,Description)
    End
    
    Exec sp_test @Addnedum = 'A1'

    Tuesday, February 25, 2014 7:51 AM
  • HI

    Try following code

    CREATE PROC usp_dynamic_exec @where VARCHAR(Max)
    AS
    BEGIN
    DECLARE @strQ AS NVARCHAR(max)

    SET @strQ = 'Select * from yourTableName'

    IF ISNULL(@where, '') <> ''
    BEGIN
    SET @strQ = @strQ + ' Where ' + @where -- where clause
    END

    EXECUTE sp_executesql @strQ
    END
    GO

    Ref :http://technet.microsoft.com/en-us/library/ms188001.aspx

    http://www.vishalseth.com/post/2008/07/10/Dynamic-SQL-sp_executesql.aspx

    http://blog.sqlauthority.com/2013/03/28/sql-server-executing-dynamic-sql-sql-queries-2012-joes-2-pros-volume-4-query-programming-objects-for-sql-server-2012/

    Mark as Answer if you find it useful


    Shridhar J Joshi Thanks a lot

    Tuesday, February 25, 2014 7:53 AM
  • Hi Latheesh ,

    Thank you for quick reply.

    Actually there are other conditions  also present in my query, that's why I am using dynamic sql.

     This is my query .

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		<Reddy>
    -- Create date: <17/02/2014>
    -- Description:	<GET BS Attachments>
    -- =============================================
    ALTER PROCEDURE [dbo].[GetBSAttachments] @StartRowIndex AS  VARCHAR(10)
    	,@MaximumRows AS  VARCHAR(10)
    	,@AddendumNo AS NVARCHAR(254) = NULL
    	,@ClaimNo AS NVARCHAR(254) = NULL 
    	,@Description AS NVARCHAR(3000) = NULL	 
    	,@fileType AS VARCHAR(12)
    AS
    BEGIN		
    
    	DECLARE @sqlColumns AS VARCHAR(8000) = ''
    	 DECLARE @sqlCond AS VARCHAR(8000) = ''
    	DECLARE @sqlCond1 AS VARCHAR(8000) = ''
    	DECLARE @sqlLimit AS VARCHAR(500) = ''
    	 DECLARE @sqlCount AS VARCHAR(500) = ''
    
    	IF (@fileType = 'Attach')
    	 BEGIN
    		SET @sqlCond1  = 'AND FileExtension <> ''msg'''
    	END
    	 ELSE IF(@fileType = 'Email')
    	BEGIN
    		SET @sqlCond1  = 'AND FileExtension = ''msg'''
    	END
    
    	SET @sqlColumns = 'SELECT   * FROM
    	(Select id,AddendumNo,Description,ClaimNo , ROW_NUMBER() OVER (ORDER BY id) AS RowRank '
    	SET @sqlCount = 'Select COUNT(*) AS BSAttachTotalCount'
    	SET @sqlCond = ' FROM EFMS.dbo.Table1 WHERE FileExtension IS NOT NULL ' + @sqlCond1 + '
    
    	' Have to modify this logic
    	AND (AddendumNo=  ''' + @AddendumNo + ''')
    	AND (''' + @ClaimNo + ''' IS NOT NULL AND ClaimNo=  ''' + @ClaimNo + ''' OR ''' + @ClaimNo + '''='''')	 
    	AND (''' + @Description + ''' IS NOT NULL AND Description like ''%' + @Description + '%''  OR ''' + @Description + '''='''')
     
    	SET @sqlLimit = ' ) AS BSAttachmentsWithRowNumbers WHERE RowRank BETWEEN ''' + CONVERT(NVARCHAR(10), @StartRowIndex) + ''' AND ''' + CONVERT(NVARCHAR(10), @MaximumRows) + ''''
    
    	EXEC (@sqlColumns + @sqlCond + @sqlLimit) ' To show in the grid by page size 	 
    
    	EXEC (@sqlCount + @sqlCond) ' To get total records count with selection criteria.
    		 
    
    
    
    END
    
    

    Tuesday, February 25, 2014 8:06 AM
  • something like below

    WHERE (AddendumNo = @A1 OR @A1='')
    AND (ClaimNo = @C1 OR @C1 = '')
    AND Description = @D1 OR @D1 = '')

    and in application set default value of textboxes as blank.

    No need of dynamic sql


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by Narsa Tuesday, February 25, 2014 9:08 AM
    Tuesday, February 25, 2014 8:11 AM
  • Try the below and show us the Print text value: (I could not test it)

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		<Reddy>
    -- Create date: <17/02/2014>
    -- Description:	<GET BS Attachments>
    -- =============================================
    ALTER PROCEDURE [dbo].[GetBSAttachments] @StartRowIndex AS  VARCHAR(10)
    	,@MaximumRows AS  VARCHAR(10)
    	,@AddendumNo AS NVARCHAR(254) = NULL
    	,@ClaimNo AS NVARCHAR(254) = NULL 
    	,@Description AS NVARCHAR(3000) = NULL	 
    	,@fileType AS VARCHAR(12)
    AS
    BEGIN		
    
    	DECLARE @sqlColumns AS VARCHAR(8000) = ''
    	 DECLARE @sqlCond AS VARCHAR(8000) = ''
    	DECLARE @sqlCond1 AS VARCHAR(8000) = ''
    	DECLARE @sqlLimit AS VARCHAR(500) = ''
    	 DECLARE @sqlCount AS VARCHAR(500) = ''
    
    	Set @AddendumNo = (Select Case when LEN(LTRIM(RTRIM(@AddendumNo))) = 0 Then NULL Else @AddendumNo End)
    	Set @Claimno = (Select Case when LEN(LTRIM(RTRIM(@Claimno))) = 0 Then NULL Else @Claimno End)
    	Set @Description = (Select Case when LEN(LTRIM(RTRIM(@Description))) = 0 Then NULL Else @Description End)
    	
    	
    	IF (@fileType = 'Attach')
    	 BEGIN
    		SET @sqlCond1  = 'AND FileExtension <> ''msg'''
    	END
    	 ELSE IF(@fileType = 'Email')
    	BEGIN
    		SET @sqlCond1  = 'AND FileExtension = ''msg'''
    	END
    
    	SET @sqlColumns = 'SELECT   * FROM
    	(Select id,AddendumNo,Description,ClaimNo , ROW_NUMBER() OVER (ORDER BY id) AS RowRank '
    	SET @sqlCount = 'Select COUNT(*) AS BSAttachTotalCount'
    	SET @sqlCond = ' FROM EFMS.dbo.Table1 WHERE FileExtension IS NOT NULL ' + @sqlCond1 + '
    
    	-- Have to modify this logic
    	AND (AddendumNo=  ISNULL(''' + @AddendumNo + ''',AddendumNo))
    	AND (Claimno=  ISNULL(''' + @Claimno + ''',Claimno))
    	AND ((''' + @Description + ''' IS NOT NULL AND Description like ''%' + @Description + '%'')  OR (''' + @Description + ''' is null and Description=Description''))'
     
    	SET @sqlLimit = ' ) AS BSAttachmentsWithRowNumbers WHERE RowRank BETWEEN ''' + CONVERT(NVARCHAR(10), @StartRowIndex) + ''' AND ''' + CONVERT(NVARCHAR(10), @MaximumRows) + ''''
    
    
    	Print @sqlColumns + @sqlCond + @sqlLimit -- To show in the grid by page size 	 
    	Print 'Another query'
    	Print (@sqlCount + @sqlCond) -- To get total records count with selection criteria.
    		 
    
    
    
    END

    • Marked as answer by Narsa Tuesday, February 25, 2014 9:08 AM
    Tuesday, February 25, 2014 8:25 AM