Sql where condition with multiple condition

Question

0.00/5 (No votes)

See more:

I have a SQL query like below: My requirement is If Development env is DEV, then condition in where clause should be Date<Getdate-500 otherwise condition should be Date<Getdate-100. all other conditions will be same in both cases. How can we do this. Thanks

Insert Into @TableA (ID)
Select distinct(ID)
from EMP
Where (Act_flag=1 AND Date<Getdate-100) OR Act_flag=0

What I have tried:

I have tried with 2 insert statements, But need to achieve this using in single Insert statement usind CASE stmt.

Posted 27-May-21 18:15pm

jaiswal Preeti

Updated 27-May-21 20:39pm

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-05-27T20:19:00

Assuming that Act_flag determines environment, then it's simple: just complete the WHERE clause:

SQL

... WHERE (Act_flag = 1 AND Date < Getdate - 100) 
       OR (Act_flag = 0 AND Date < Getdate - 500)

But the problem is that you can't specify a variable as the table name: SQL will not let you.

To do that, you'd have to create a string using the variable:

SQL

SET @cmd = 'INSERT INTO ' + @TableA + ' (ID) ... ';
EXEC (@cmd)

But that's dangerous! Depending on where you got @TableA from, you could be wide open to Sql Injection, which can compromise, damage, or destroy your database.

And I'm a little concerned that you use production / development in SQL code - I hope like heck you aren't developing on the same database as the production system uses, because if you do ... Well, you'll find out and your boss(es) will drop on you like a ton of bricks when you make a tiny mistake with the production data ... you do need to use a totally separate system from dev testing, partly for data protection / GDPR reasons, mostly for "oops, I deleted the orders table" reasons.

jaiswal Preeti · Answer 2 · 2021-05-27T20:39:00

Solution 2

Thanks Permalink,

Environment we can read from a table assign It to a variable @Env.

Can we use CASE statement here? If Yes How? Thanks.

Posted 27-May-21 20:39pm

jaiswal Preeti

Comments

Richard Deeming 28-May-21 4:36am

If you want to ask for more information, click the "Have a Question or Comment?" button under the solution and post a comment.

Do not post your comment as a new "solution".

Sql where condition with multiple condition

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Sql where condition with multiple condition

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us