DECLARE @CurrentDateTime DATETIME

SET @CurrentDateTime = Getdate()

DECLARE @Nth AS BIT
DECLARE @Aus INT
DECLARE @Ante NVARCHAR(255)
DECLARE @Atid INT
DECLARE @Adid INT
DECLARE @Brid INT
DECLARE @Tid INT
DECLARE @TeID INT
DECLARE @Rkey NVARCHAR(20)

SET @Rkey= '123456'

--Get Exception Info                                                  
SELECT @Tid = Tid
FROM   Synonym_Tes
WHERE  [ID] = @TeID

SELECT TOP(1)@Atid = Tid
FROM   Synonym_Tasks
WHERE  [Name] = 'Azn'

SET @Nth = 0

--  SET @Adid= 64    
SELECT @Adid= brt.Dsid,
       @Brid = Id
FROM   Synonym_Brt brt
WHERE  brt.Rtn = 'Part 1'

---- Is an auth needed?                              
IF EXISTS (SELECT 1
           FROM   tempdb..sysobjects
           WHERE  id = Object_id('tempdb..#Fds'))
  DROP TABLE #Fds

IF EXISTS (SELECT 1
           FROM   tempdb..sysobjects
           WHERE  id = Object_id('tempdb..#Rls'))
  DROP TABLE #Rls

IF EXISTS (SELECT 1
           FROM   tempdb..sysobjects
           WHERE  id = Object_id('tempdb..#Mtcs'))
  DROP TABLE #Mtcs
  
  
  
 CREATE TABLE #Sid
    (
       ID INT
    )
    

CREATE TABLE #Fds
  (
     Dsid INT,
    Rid    INT,
     Fnme   VARCHAR(100),
     Fve  VARCHAR(255)
  )

CREATE TABLE #Rls
  (
     Dsid    INT,
     Brid INT,
     Fnme      VARCHAR(100),
     Fve     VARCHAR(MAX),
     FCprs VARCHAR(30),
     Fopr  VARCHAR(10)
  )

CREATE TABLE #Mtcs
  (
     Dsid   INT,
    Rid      INT,
     Brid INT
  )

DECLARE @CidINT
DECLARE @flag INT
DECLARE @Count INT

SET @flag = 1

SELECT @Count = Count(Rid)
FROM   Cgs
WHERE  Rid = @Rkey

WHILE ( @flag <= @Count )
  BEGIN
      SELECT @Cid= Id
      FROM   Cgs
      WHERE  Rid = @Rkey
             AND Cordr = @flag


      INSERT INTO #Fds
                  (Dsid,
                   Rid,
                   Fnme,
                   Fve)
      SELECT @AuthDsid,
             Rid,
             Fnme,
             Fve
      FROM   vw_FdsAzn
      WHERE Rid= @Cid

      SET @flag = @flag + 1
  END

INSERT INTO #Rls
            (Dsid,
             Brid,
             Fnme,
             Fve,
             FCprs,
             Fopr)
SELECT @AuthDsid,
       br.Id,
       brc.Fnme,
       Fve= CASE
                      WHEN brc.FCprs IN ( 'INLE', 'NOT-BLE' ) THEN [dbo].[my_function](brc.Fve, brc.FCprs)
                      ELSE brc.Fve
                    END,
       brc.FCprs,
       dsf.Fopr
FROM   Synonym_BRls br
       JOIN Synonym_Brc brc
         ON brc.Brid= br.Id
       JOIN Synonym_Brt brt
         ON brt.Id = br.Brid
       JOIN Synonym_DataSourceFds dsf
         ON dsf.Fnme= brc.Fnme
            AND dsf.Dsid = brt.Dsid
WHERE  br.Atve= 1
       AND br.Brid = @Brid 
       
                                  



DECLARE @dlm CHAR

SET @dlm = ','

SELECT Rid,
       Dsid,
       Brid,
       Fnme,
       CompareFrom,
       FCprs,
       Fopr,
       CompareTo,
       Passed
INTO   #FCprss
FROM   (SELECT r.Brid,
               r.Fnme,
               r.FveAS CompareTo,
               r.FCprs,
               r.Fopr,
               f.FveAS CompareFrom,
               f.Rid,
               f.Dsid,
               CASE
                 WHEN ( ( r.FCprs = 'EQS'
                          AND ( f.Fve= r.Fve) )
                         OR ( r.FCprs = 'DOEL'
                              AND r.Fopr<> 'OR'
                              AND ( f.Fve<> r.Fve) )
                         OR ( r.FCprs = 'COS'
                              AND ( f.FveLIKE '%' + r.Fve+ '%' ) )
                         OR ( r.FCprs = 'DON'
                              AND r.Fopr<> 'OR'
                              AND ( f.FveNOT LIKE '%' + r.Fve+ '%' ) )
                         OR ( r.FCprs = 'BETH'
                              AND ( f.FveLIKE r.Fve+ '%' ) )
                         OR ( r.FCprs = 'DOTH'
                              AND r.Fopr<> 'OR'
                              AND ( f.FveNOT LIKE r.Fve+ '%' ) )
                         OR ( r.FCprs = 'ENH'
                              AND ( f.FveLIKE '%' + r.Fve) )
                         OR ( r.FCprs = 'DOTH'
                              AND r.Fopr<> 'OR'
                              AND ( f.FveNOT LIKE '%' + r.Fve) )
                         OR ( r.FCprs = 'ISNULL'
                              AND f.FveIS NULL )
                         OR ( r.FCprs = 'DOAL'
                              AND r.Fopr= 'OR'
                              AND ( Isnull(f.Fve, '') <> r.Fve) )
                         OR ( r.FCprs = 'DOEIN'
                              AND r.Fopr= 'OR'
                              AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve+ '%' ) )
                         OR ( r.FCprs = 'DOESITH'
                              AND r.Fopr= 'OR'
                              AND ( Isnull(f.Fve, '') NOT LIKE r.Fve+ '%' ) )
                         OR ( r.FCprs = 'DOETH'
                              AND r.Fopr= 'OR'
                              AND ( Isnull(f.Fve, '') NOT LIKE '%' + r.Fve) )
                         OR ( r.FCprs = 'ISNUMERIC'
                              AND Isnumeric(f.Fve) = 1 )
                         OR ( r.FCprs = 'BETWEEN'
                              AND f.FveBETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) )
                         OR ( r.FCprs = 'NOTBETWEEN'
                              AND f.FveNOT BETWEEN Ltrim(Substring(r.Fve, 0, Charindex(',', r.Fve))) AND Ltrim(Substring(r.Fve, Charindex(',', r.Fve) + 1, 255)) )
                         OR ( r.FCprs = 'NOTIN'
                              AND r.Fopr<> 'OR'
                              AND t.ID IS NULL
                              AND f.FveIS NOT NULL )
                         OR ( r.FCprs = 'NOTIN'
                              AND r.Fopr= 'OR'
                              AND t.ID IS NULL )
                         OR ( r.FCprs = 'IN'
                              AND t.ID IS NOT NULL )
                         OR ( r.FCprs = 'ntble'
                              AND r.Fopr<> 'OR'
                              AND t.ID IS NULL
                              AND f.FveIS NOT NULL )
                         OR ( r.FCprs = 'ntble'
                              AND r.Fopr= 'OR'
                              AND t.ID IS NULL )
                         OR ( r.FCprs = 'INTABLE'
                              AND t.ID IS NOT NULL ) ) THEN 1
                 ELSE 0
               END          AS Passed
        FROM   #Rls r
               LEFT JOIN #Fds f
                 ON r.Fnme= f.Fnme
               LEFT JOIN #Sid t
                 ON r.FCprs IN ( 'IN', 'NOTIN', 'INTABLE', 'ntble' )
                    AND t.ID <= Len(@dlm + r.Fve+ @dlm)
                    AND Substring(@dlm + r.Fve+ @dlm, t.ID - 1, 1) = @dlm -- i need to move this on the top where there is join on # Sid table.
                    AND Ltrim(Rtrim(Substring(@dlm + r.Fve+ @dlm, t.ID, Charindex(@dlm, @dlm + r.Fve+ @dlm, t.ID) - t.ID))) = f.Fve) a