In where condition use column value which contains column name

Question

Post reply

In where condition use column value which contains column name

rohit.kumar.barik

Say Hey Kid

Points: 672
More actions
July 8, 2020 at 2:00 pm
Go to Answer
#3768400

Hi Team,
Greetings!
I have below table. let table name =Emp
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: 246960

July 8, 2020 at 2:42 pm

Answer

#3768435

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)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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)?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

rohit.kumar.barik Say Hey Kid Points: 672 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 3

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: 442467 More actions · Answer 4

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.