Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 how to exclude table and conditions from cursor sq

Author  Topic 

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2014-04-14 : 11:35:32

How to exclude the B. related table join and its conditions , if agency =1, else use them if agency <>1, using straight query without dynamic sql string is it possible to handle agency based condition.

IF @AgencyID = 1



DECLARE CUR_TEMP SCROLL CURSOR
FOR SELECT A.RMID FROM TAB_RM_log AS A JOIN TAB_UserAccess AS B
ON (A.UpdatedBy = B.UserName AND A.ProgID = B.ProgID AND A.ProjID = B.ProjID AND A.ContractID = B.ContractID)
JOIN TAB_RM AS C ON (A.RMID = C.RMID)
WHERE B.UserName = @UserName
AND A.Deleted = '0' AND B.Deleted = '0' AND C.Deleted = '0' AND B.NetModule = 'RM' ORDER BY A.Updated






Thank you very much for the helpful info.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-14 : 13:41:50
Without knowing more, the best I can suggest is:

IF @AgencyID = 1
DECLARE CUR_TEMP SCROLL CURSOR
FOR SELECT A.RMID FROM TAB_RM_log AS A
JOIN TAB_RM AS C ON (A.RMID = C.RMID)
WHERE A.Deleted = '0' AND C.Deleted = '0' ORDER BY A.Updated
ELSE
DECLARE CUR_TEMP SCROLL CURSOR
FOR SELECT A.RMID FROM TAB_RM_log AS A JOIN TAB_UserAccess AS B
ON (A.UpdatedBy = B.UserName AND A.ProgID = B.ProgID AND A.ProjID = B.ProjID AND A.ContractID = B.ContractID)
JOIN TAB_RM AS C ON (A.RMID = C.RMID)
WHERE B.UserName = @UserName
AND A.Deleted = '0' AND B.Deleted = '0' AND C.Deleted = '0' AND B.NetModule = 'RM' ORDER BY A.Updated


Go to Top of Page
   

- Advertisement -