In where condition use column value which contains column name

  • rohit.kumar.barik

    Say Hey Kid

    Points: 672

    Hi Team,

    Greetings!

    I have below table. let table name =Emp

    Capture

    I need to query in the table like below.

    select * from Emp

    where (Here instead of writing country='India', I want to use the column business_rule directly so that i can get the records )

    Thanks in advance!

  • Phil Parkin

    SSC Guru

    Points: 244662

    You'll be needing to use dynamic SQL to make that happen.

    But if all of your business rules are like that, why not just join on (Name, Country)?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • rohit.kumar.barik

    Say Hey Kid

    Points: 672

    Business rule column in not a consistent one. I have given two columns as example. But it may contain 2, 3 or more column names under business_rule.

    Yes, I tried with dynamic sql but could not make it. So if any suggestion how to build the query

    Thanks

    Rohit

  • Phil Parkin

    SSC Guru

    Points: 244662

    Note that the following is vulnerable to SQL Injection. If your business rules table is at any risk of being compromised, don't use this code.

    DROP TABLE IF EXISTS #Rule;

    CREATE TABLE #Rule
    (
    Name VARCHAR(50) NOT NULL
    ,Country VARCHAR(100) NOT NULL
    ,BusinessRule VARCHAR(1000) NOT NULL
    );

    INSERT #Rule
    (
    Name
    ,Country
    ,BusinessRule
    )
    VALUES
    ('RK', 'India', 'Name = ''RK'' and Country = ''India''');

    SELECT *
    FROM #Rule r;

    DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM EMP WHERE ' +
    (
    SELECT TOP (1) r.BusinessRule FROM #Rule r WHERE r.Country = 'India'
    );

    SELECT @SQL;

    --EXEC (@SQL)

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720484

    In a SELECT, there is no late binding of columns. You have to use dynamic SQL, as Phil noted, but there are all sorts of security problems here. You are risking your system here by trying to store business rules like this.

  • Lynn Pettis

    SSC Guru

    Points: 442342

    rohit.kumar.barik wrote:

    Business rule column in not a consistent one. I have given two columns as example. But it may contain 2, 3 or more column names under business_rule.

    Yes, I tried with dynamic sql but could not make it. So if any suggestion how to build the query

    Thanks

    Rohit

    Please post the dynamic SQL you tried using as well as the issue(s) you experienced.

     

Viewing 6 posts - 1 through 6 (of 6 total)

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