Click here to Skip to main content
15,041,548 members
Please Sign up or sign in to vote.
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
Updated 27-May-21 20:39pm

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.
   
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.
   
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".

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900