Build search condition dynamic

  • /*-------------------------Drop temp tables-------------------------------------------*/

    IF OBJECT_ID('tempdb..#test') IS NOT NULL

    DROP TABLE #test

    CREATE TABLE #test (TestID CHAR(5) NOT NULL PRIMARY KEY)

    INSERT INTO #test

    SELECT '1'

    UNION ALL

    SELECT '2'

    UNION ALL

    SELECT '15'

    UNION ALL

    SELECT 'ab1'

    UNION ALL

    SELECT 'bc1'

    UNION ALL

    SELECT 'cd1'

    UNION ALL

    SELECT 'uv5'

    SELECT *

    FROM #test

    WHERE (-- below should be dynamic assigned to a variable

    testid = '1'

    OR testid = '2'

    OR testid = '15'

    OR testid = 'ab1'

    OR testid = 'bc1'

    OR testid = 'cd1'

    OR testid = 'uv5'

    )

    i am trying to build a dynamic where "or" clause finding difficulties any help would be appreciated

  • Rather than building up a static or dynamic list of OR conditions in WHERE clause, instead load your search keywords into a temp table or table variable, and then join.

    select *

    from TestTable

    join @SearchKeywords S

    on S.keyword = TestTable.keyword;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • sorry i put question wrong way i need as below. when user chooses a field and condition i need to build string dynamically

    drop table #temp

    create table #temp

    (

    Col1 char(10) --colname

    ,Col2 char(1) -- left

    ,Col3 char(1)--write

    ,Col4 VARCHAR(72) -- uid

    ,Col5 char(2) -- conditional operator

    )

    insert into #temp

    select 'col1', '(',')','pqr','or'

    union all select 'col2', '(',')','xyz','or'

    union all select 'col3', '(',')','cef','or'

    union all select 'col4', '(',')','pqr','or'

    select * from #temp

    O/p should be of where clause

    where (col1 = 'pqr')

    or (col2 = 'xyz')

    or (col2 = 'cef')

    or (col2 = 'pqr')

  • Sounds like you may be talking about optional parameters...

    DECLARE

    @BusinessEntityID INT = NULL,

    @FirstName VARCHAR(50) = 'John',

    @LastName VARCHAR(50) = NULL;

    SELECT

    p.BusinessEntityID,

    p.PersonType,

    p.NameStyle,

    p.Title,

    p.FirstName,

    p.MiddleName,

    p.LastName

    FROM

    Person.Person p

    WHERE

    (p.BusinessEntityID = @BusinessEntityID OR @BusinessEntityID IS NULL)

    AND (p.FirstName = @FirstName OR @FirstName IS NULL)

    AND (p.LastName = @LastName OR @LastName IS NULL)

    OPTION(RECOMPILE)

    ;

  • thanks but optional parameters are choose by user we need to build where clause based on user selection for each report

  • http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Pass the values in as a comma separated string, and join to or use where exists to the above referenced function.

    FROM Table X

    INNER JOIN DelimitedSplit8k('parameter values', ',') S ON X.field = S.Item

    OR

    FROM Table X

    WHERE EXISTS (SELECT S.Item FROM DelimitedSplit8k('parameter values', ',') S WHERE S.Item = X.field

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • mxy (10/6/2015)


    thanks but optional parameters are choose by user we need to build where clause based on user selection for each report

    You haven't told us how you plan to have the users choose the parameters they want to use.

    The code I provided in my last post, creates a parameter for each of the available options and the user fills in values for the ones they want to use and leave the rest null (not used).

  • This has the trademark appearance of a catch-all query. You need to read this article about this type of query so you understand some options for performance. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply