CREATE PROC DBO.GEXBPM_CHECKSETUP
(
  @BILLTAG   VARCHAR(20)
)
/*
  Function   : 檢查 FLOW 的設定是否正確
  Description: 目前只能手動在 SQL 中執行, 如果要加到程式中, 要請 RD 手寫程式
  Build Date : 2013/08/01
  
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
    
*/
AS

DECLARE @FILTER   NVARCHAR(2000)
DECLARE @SQL2     NVARCHAR(2000)
DECLARE @SOURCETABLE  VARCHAR(100)
DECLARE @RETVAL   INT  --sp 傳參數 
DECLARE @SEQNO    INT

DECLARE contact_cursor CURSOR FOR
SELECT A.SEQNO,A.FILTER,B.SOURCETABLE FROM BPM_FLOWSETUP_D A
LEFT JOIN BPM_FLOWSETUP_M B ON A.BILLTAG = B.BILLTAG
WHERE B.BILLTAG = @BILLTAG AND ISNULL(A.FILTER,'') <> '' 
ORDER BY 1
  
OPEN contact_cursor

-- Perform the first fetch.
FETCH NEXT FROM contact_cursor
INTO @SEQNO,@FILTER,@SOURCETABLE

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @SQL2 = 'SELECT @RETVALIN=COUNT(*) FROM ' + @SOURCETABLE + ' WHERE ' + @FILTER
  EXECUTE SP_EXECUTESQL @SQL2,N'@RETVALIN INT OUT', @RETVAL OUT

  PRINT 'SEQNO: ' + CAST(@SEQNO AS VARCHAR(10)) + ' FILTER: ' + @FILTER
  
  FETCH NEXT FROM contact_cursor
  INTO @SEQNO,@FILTER,@SOURCETABLE
END

CLOSE contact_cursor
DEALLOCATE contact_cursor