How to write SQL statement with dynamic where clause?

Transact SQL Question. Needed: code based on the supplied sql statement.

• How would I use a trigger, in SQL Server 2000 to solve the following problem? I need a trigger that will do the following: As data is imported from a stored procedure into a local database I want it to do two things: 1. As my data is imported from the stored procedure, I want it to change on-the-fly, to relocate the data. - I want the data that normally would be inserted into JOB_NO_TBC, to insert into WO_KEY_TBC, row-by-row as it is inserted. 2. I want it to perform this task only if the date is > 11/23/2002 There are specific reasons that I just cannot change the insert clause in the sql. I’m also just wondering how the transact sql should look. I want an example based on the following query: IE. Here is an example of 1 part of my query: INSERT INTO dbo.CsgResultsTbcDts SELECT distinct * FROM OPENQUERY(csg, ' SELECT SYS_TBC , PRIN_TBC , AGNT_TBC , SUB_ACCT_NO_TBC , ADDR1_HSE , RES_CITY_HSE , MGMT_AREA_HSE , JOB_NO_TBC , TOT_UNITS_TBC , WO_RSN_TBC , CLS_OUT_RSN_TBC , ORIG_OPER_ID_TBC , IR_TECH_TBC , TIME_FLG_TBC , LS_CHG_DTE_TBC , LS_CHG_OPER_ID_TBC , LS_CHG_TERM_ID_TBC , WO_KEY_TBC , WO_STAT_TBC , WO_CLASS_TBC , WO_TYP_TBC , SCHED_CAT_TBC , DISPATCH_STAT_TBC , HSE_KEY_HSE , PRIN_HSE , SYS_HSE , res_state_hse FROM TBC_BASE, HSE_BASE where SUBSTR( WO_KEY_TBC, 1, 14) = HSE_KEY_HSE and LS_CHG_DTE_TBC >= SYSDATE -3 AND PRIN_TBC = PRIN_HSE AND SYS_TBC = SYS_HSE AND SYS_TBC = 8773') WHERE SUBSTRing(WO_KEY_TBC, 1, 14) = HSE_KEY_HSE AND (SUBSTRing(WO_RSN_TBC, 1, 2) = '42' OR SUBSTRing(WO_RSN_TBC, 3, 2) = '42' OR SUBSTRing(WO_RSN_TBC, 5, 2) = '42' OR SUBSTRing(WO_RSN_TBC, 7, 2) = '42') AND WO_STAT_TBC IN ('C', 'D' , 'X', 'H', 'O', 'R') and not exists (select * from CsgResults_Master where WO_KEY_WoAndTbc = WO_KEY_TBC and ls_chg_dte_WoAndTbc = ls_chg_dte_tbc and wo_stat_woandtbc = wo_stat_tbc) Thank you, -Peter

• Answer:

  Hi, pmclinn-ga: Here is the sample trigger and supporting table/test records used. To accomplish your goal I used an "INSTEAD OF" trigger on the INSERT action, a feature that become available in SQL Server 2000. First the table creation language repeated for the sake of completeness: CREATE TABLE tblNeedsTrigger ( MY_1St_COL int, JOB_NO_TBC int, WO_KEY_TBC varchar(20), MY_CHG_DTE datetime ) GO Second the actual trigger creation language: CREATE TRIGGER trgInsteadOfInsert on tblNeedsTrigger INSTEAD OF INSERT AS INSERT into tblNeedsTrigger SELECT MY_1St_COL, JOB_NO_TBC, CASE WHEN MY_CHG_DTE > '11/23/2002' THEN CONVERT(varchar(20),JOB_NO_TBC) ELSE WO_KEY_TBC END, MY_CHG_DTE FROM inserted Now some test records. This first test statement just causes the indicated values to be inserted into the base table, because the "change date" doesn't exceed the threshold: INSERT into tblNeedsTrigger values (1,412,'214','10/10/2000') This next statement causes the modification of WO_KEY_TBC to take place: INSERT into tblNeedsTrigger values (2,412,'214','10/10/2003') so that WO_KEY_TBC stores '412' rather than '214'. I'm not quite clear what you meant by having the trigger delete records. I'm fairly sure this can be done, but would need to have more details. If what you are thinking of is to prevent some records from being inserted (rather than actually deleting records that already exist), it can be easily accomplished by adding a date range criterion as a WHERE clause in the body of the trigger. regards, mathtalk-ga