49 lines
1.3 KiB
Plaintext
49 lines
1.3 KiB
Plaintext
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
|