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: 246692

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)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 734434 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.