nghinv
Joined: 18 Nov 2009
Posts: 4
|
 Posted: Wed Nov 18, 2009 8:11 am Post subject: Find unused variables does not work properly with TRY~CATCH |
 |
|
Hi,
I have trouble with SQL Refactor 1.3.027. The function does not work properly. Detail information described as below:
1. I Have a STP and I want to set Debugtrace for each importance step by setting debug trace to @debugTrace variable. The SP implement TRY~CATCH, if there is error occurs I will catch it and show the @debugTrace message. When run the function Find Unused variables there is always message say that this values set here nerver been used, this is a defect because when error occurs in BEGIN TRY ~ END TRY, the CATCH clause with deal with it. The sample SP as below:
| Code: |
IF EXISTS ( SELECT *
FROM sys.objects
WHERE name = 'abc'
AND type = 'P'
AND USER_NAME(schema_id) = 'dbo' )
BEGIN
DROP PROC abc
END
GO
CREATE PROC [dbo].[abc]
AS
BEGIN
DECLARE @tbl TABLE ( A VARCHAR(100) )
DECLARE @debugTrace VARCHAR(1000)
BEGIN TRY
-- SET A DEBUG TRACE
SET @debugTrace = 'INSERT DATA TO THE TABLE @tbl'
INSERT @tbl SELECT TOP 10 column_name FROM INFORMATION_SCHEMA.COLUMNS
-- SET A DEBUG TRACE
SET @debugTrace = 'DELETE ALL THE DATA FROM @tbl'
DELETE FROM @tbl
-- ...
END TRY
BEGIN CATCH
SELECT @debugtrace
END CATCH
END
GO |
2. I found that the find unused variables does not working properly with not very complex SP. I declare variables and used it as constants but the function does not work well. please validate below SP:
| Code: |
IF EXISTS ( SELECT * FROM sys.objects WHERE name = 'STP_SHP07MDlvCorpRegistUpd' AND type = 'P' AND user_name(schema_id) = 'dbo')
BEGIN
DROP PROC STP_SHP07MDlvCorpRegistUpd
END
GO
CREATE PROC [dbo].[STP_SHP07MDlvCorpRegistUpd]
(
@DeliveryCompanyCode CHAR ( 2 )
, @UserCd VARCHAR ( 13 )
, @ControlDate INT
, @UpdateJobMod VARCHAR ( 80 )
, @Success INT OUTPUT
, @ErrCode INT OUTPUT
, @ErrMsg VARCHAR ( 250 ) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
--==============================================================================
--== Constants
--==============================================================================
-- Constant for SUCCESS
DECLARE @ct_SUCCESS_SUCCESS INT = 0
DECLARE @ct_SUCCESS_ERROR INT = 3
DECLARE @ct_SQLERROR_NUMBER INT = 0
-- On/Off Flag
DECLARE @ct_OnFlg INT = 1
DECLARE @ct_OffFlg INT = 0
-- Message Kbn
DECLARE @ct_MESSAGEKBN_BOT VARCHAR(10) = 'BOT'
DECLARE @ct_MESSAGEKBN_INS VARCHAR(10) = 'INS'
DECLARE @ct_MESSAGEKBN_UPD VARCHAR(10) = 'UPD'
DECLARE @ct_MESSAGEKBN_EOT VARCHAR(10) = 'EOT'
-- Constant for the message code
DECLARE @ct_MSG_SYSBOT VARCHAR ( 10 ) = '110033'
DECLARE @ct_MSG_SYSEOT VARCHAR ( 10 ) = '110034'
DECLARE @ct_MSG_SYSERR41 VARCHAR ( 10 ) = '110041'
DECLARE @ct_MSG_SYSERR42 VARCHAR ( 10 ) = '110042'
DECLARE @ct_MSG_MSG002 VARCHAR ( 10 ) = '110036'
-- Capture processes for type FLC stock information stock
DECLARE @ct_NONFATALEXCEPTION INT = 16
DECLARE @ct_RAISERRORSTATE INT = 1
DECLARE @ct_STP_ID VARCHAR ( 30 ) = 'STP_SHP07MDlvCorpRegistUpd'
DECLARE @ct_MDLVCORP_NAME VARCHAR ( 30 ) = '配送会社台帳'
-- Ship Center Kbn
DECLARE @ct_SHIPCENTERCD_1_FLC VARCHAR(1) = '1' -- FLC
DECLARE @ct_SHIPCENTERCD_2_LDC VARCHAR(1) = '2' -- LDC
-- 着店対象区分
DECLARE @ct_ARIVDSHOPTGTKBN_2 CHAR(1) = '2'
DECLARE @ct_ARIVDSHOPTGTKBN_4 CHAR(1) = '4'
DECLARE @ct_ARIVDSHOPTGTKBN_5 CHAR(1) = '5'
DECLARE @ct_ARIVDSHOPTGTKBN_6 CHAR(1) = '6'
DECLARE @ct_ARIVDSHOPTGTKBN_7 CHAR(1) = '7'
DECLARE @ct_ARIVDSHOPTGTKBN_8 CHAR(1) = '8'
--==============================================================================
--== Variable declaration
--==============================================================================
DECLARE @TSHP07010I_01_InputSeq INT = 0
DECLARE @TSHP07010I_01_ZipNo CHAR ( 7 ) = ''
DECLARE @TSHP07010I_01_TraderCd CHAR ( 3 ) = ''
DECLARE @TSHP07010I_01_SortCd CHAR ( 7 ) = ''
DECLARE @TSHP07010I_01_DlvStdDays TINYINT = 0
DECLARE @TSHP07010I_01_DlvDate CHAR ( 2 ) = ''
DECLARE @TSHP07010I_01_DlvTime CHAR ( 2 ) = ''
DECLARE @TSHP07010I_01_CnstInstallTraderCd CHAR ( 3 ) = ''
DECLARE @TSHP07010I_01_CnstInstallSortCd CHAR ( 7 ) = ''
DECLARE @TSHP07010I_01_CnstInstallDlvDate CHAR ( 2 ) = ''
DECLARE @TSHP07010I_01_CnstInstallDlvTime CHAR ( 2 ) = ''
DECLARE @TSHP07010I_01_ArivdShopCd CHAR ( 1 ) = ''
DECLARE @TSHP07010I_01_ShipCenterKbn CHAR ( 2 ) = ''
DECLARE @TSHP07010I_01_AreaTraderCd CHAR ( 3 ) = ''
-- Local meaning vairables
DECLARE @w_MessageList VARCHAR ( 250 ) = ''
DECLARE @w_Ins_MDlvCorpCnt INT = 0
DECLARE @w_Upd_MDlvCorpCnt INT = 0
DECLARE @w_Ins_MDlvCorpCommitCnt INT = 0
DECLARE @w_Upd_MDlvCorpCommitCnt INT = 0
DECLARE @w_c_TSHP07010IOpenFlg INT = @ct_OffFlg
-- Output arguments for STP_GetApMessage
DECLARE @w_Message VARCHAR ( 1024 ) = '' -- CATCH Output message
--==============================================================================
--== Ouput parameter
--==============================================================================
SET @Success = 0 -- Processing success or failure flag (framework standard parameter)
SET @ErrCode = 0 -- Error code (framework standard parameter)
SET @ErrMsg = 0 -- Error message (framework standard parameter)
--==============================================================================
--== Initialize the output parameters
--==============================================================================
SET @Success = @ct_SUCCESS_SUCCESS
SET @ErrCode = @ct_SQLERROR_NUMBER
SET @ErrMsg = ''
SET @w_MessageList = ''
BEGIN TRY
--==================================================
--== Start logging
--==================================================
-- Log output
EXECUTE FW_STP_WriteLog @ct_MESSAGEKBN_BOT, @ct_MSG_SYSBOT, @w_MessageList, @UpdateJobMod, @ct_STP_ID, @UserCd
--==============================================================================
--== Begin transaction
--==============================================================================
BEGIN TRAN
-- Set a debug trace
SET @w_Message = 'SELECT THE FIRST RECORD FROM TSHP07010I_01, InputSeq = 1'
-- Select the first record FROM TSHP07010I_01
SELECT
@TSHP07010I_01_ArivdShopCd = SUBSTRING( ZipNo, 1, 1 )
, @TSHP07010I_01_AreaTraderCd = SUBSTRING( ZipNo, 2, 3 )
, @TSHP07010I_01_ShipCenterKbn = ( CASE WHEN SUBSTRING ( ZipNo, 1, 1 ) = @ct_ARIVDSHOPTGTKBN_2
OR SUBSTRING ( ZipNo, 1, 1 ) = @ct_ARIVDSHOPTGTKBN_6
THEN @ct_SHIPCENTERCD_1_FLC
ELSE @ct_SHIPCENTERCD_2_LDC
END )
FROM
TSHP07010I_01
WHERE
InputSeq = 1
-- Set a debug trace
SET @w_Message = 'DECLARE c_TSHP07010I CURSOR, InputSeq > 1'
-- Declare CURSOR
DECLARE c_TSHP07010I INSENSITIVE CURSOR
FOR SELECT
InputSeq
, ZipNo
, TraderCd
, SortCd
, DlvStdDays
, DlvDate
, DlvTime
, CnstInstallTraderCd
, CnstInstallSortCd
, CnstInstallDlvDate
, CnstInstallDlvTime
FROM
TSHP07010I_01
WHERE
InputSeq > 1
ORDER BY
InputSeq
-- Set a debug trace
SET @w_Message = 'OPEN THE CURSOR c_TSHP07010I'
-- Open cursor
OPEN c_TSHP07010I
SET @w_c_TSHP07010IOpenFlg = @ct_OnFlg
-- Set a debug trace
SET @w_Message = 'FETCH THE FIRST ROW FROM THE CURSOR c_TSHP07010I'
-- Fetch data
FETCH NEXT FROM c_TSHP07010I INTO
@TSHP07010I_01_InputSeq
, @TSHP07010I_01_ZipNo
, @TSHP07010I_01_TraderCd
, @TSHP07010I_01_SortCd
, @TSHP07010I_01_DlvStdDays
, @TSHP07010I_01_DlvDate
, @TSHP07010I_01_DlvTime
, @TSHP07010I_01_CnstInstallTraderCd
, @TSHP07010I_01_CnstInstallSortCd
, @TSHP07010I_01_CnstInstallDlvDate
, @TSHP07010I_01_CnstInstallDlvTime
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
-- Set a debug trace
SET @w_Message = 'UPDATE MDlvCorp WITH @DeliveryCompanyCode=[' + CAST ( @DeliveryCompanyCode AS VARCHAR ( 10 ) )
+ '], @TSHP07010I_01_AreaTraderCd=[' + CAST ( @TSHP07010I_01_AreaTraderCd AS VARCHAR ( 10 ) )
+ '], @TSHP07010I_01_ZipNo=[' + CAST ( @TSHP07010I_01_ZipNo AS VARCHAR ( 10 ) )
+ '], @TSHP07010I_01_ArivdShopCd=[' + CAST ( @TSHP07010I_01_ArivdShopCd AS VARCHAR ( 10 ) ) + ']'
-- Update data into Delivery company master
UPDATE
MDlvCorp
SET
TraderCd = @TSHP07010I_01_TraderCd
, SortCd = @TSHP07010I_01_SortCd
, DlvStdDays = @TSHP07010I_01_DlvStdDays
, DlvDate = @TSHP07010I_01_DlvDate
, DlvTime = @TSHP07010I_01_DlvTime
, CnstInstallTraderCd = @TSHP07010I_01_CnstInstallTraderCd
, CnstInstallSortCd = @TSHP07010I_01_CnstInstallSortCd
, CnstInstallDlvDate = @TSHP07010I_01_CnstInstallDlvDate
, CnstInstallDlvTime = @TSHP07010I_01_CnstInstallDlvTime
, UpdateUserCd = @UserCd
, UpdateJobDate = GETDATE()
, UpdateCount = dbo.FW_FNC_NextNo ( UpdateCount )
, UpdateJobMod = @UpdateJobMod
, DelFlg = 0
WHERE
DlvCorpCd = @DeliveryCompanyCode
AND AreaTraderCd = @TSHP07010I_01_AreaTraderCd
AND (
(
ShipCenterCd = @ct_SHIPCENTERCD_2_LDC
AND (
ArivdShopTgtKbn = @ct_ARIVDSHOPTGTKBN_4
OR ArivdShopTgtKbn = @ct_ARIVDSHOPTGTKBN_5
OR ArivdShopTgtKbn = @ct_ARIVDSHOPTGTKBN_7
OR ArivdShopTgtKbn = @ct_ARIVDSHOPTGTKBN_8
)
)
OR (
ShipCenterCd = @ct_SHIPCENTERCD_1_FLC
AND (
ArivdShopTgtKbn = @ct_ARIVDSHOPTGTKBN_2
OR ArivdShopTgtKbn = @ct_ARIVDSHOPTGTKBN_6
)
)
)
AND ZipNo = @TSHP07010I_01_ZipNo
AND ArivdShopTgtKbn = @TSHP07010I_01_ArivdShopCd
-- If @@Rowcount <>0 then insert data into Delivery company master
IF ( @@ROWCOUNT <> 0 )
BEGIN
SET @w_Upd_MDlvCorpCnt += @@ROWCOUNT
END
ELSE -- If Rowcount =0 or Rowcount >1 then insert data into Delivery company master
BEGIN
-- Set a debug trace
SET @w_Message = 'INSERT INTO MDlvCorp WITH @DeliveryCompanyCode=[' + CAST ( @DeliveryCompanyCode AS VARCHAR ( 10 ) )
+ '], @TSHP07010I_01_AreaTraderCd=['+ CAST ( @TSHP07010I_01_AreaTraderCd AS VARCHAR ( 10 ) )
+ '], @TSHP07010I_01_ZipNo=[' + CAST ( @TSHP07010I_01_ZipNo AS VARCHAR ( 10 ) )
+ '], @TSHP07010I_01_ArivdShopCd=[' + CAST ( @TSHP07010I_01_ArivdShopCd AS VARCHAR ( 10 ) ) + ']'
INSERT INTO
MDlvCorp
(
ZipNo
, DlvCorpCd
, ShipCenterCd
, ArivdShopTgtKbn
, AreaTraderCd
, TraderCd
, SortCd
, StartPointCd
, ArivdShopCd
, NittsuBranchCd
, DlvStdDays
, DlvDate
, DlvTime
, CnstInstallTraderCd
, CnstInstallSortCd
, CnstInstallDlvDate
, CnstInstallDlvTime
, RegisterUserCd
, RegisterDate
, UpdateUserCd
, UpdateWinDate
, UpdateJobDate
, UpdateCount
, UpdateWinMod
, UpdateJobMod
, DelFlg
)
VALUES
(
@TSHP07010I_01_ZipNo
, @DeliveryCompanyCode
, @TSHP07010I_01_ShipCenterKbn
, @TSHP07010I_01_ArivdShopCd
, @TSHP07010I_01_AreaTraderCd
, @TSHP07010I_01_TraderCd
, @TSHP07010I_01_SortCd
, ''
, ''
, ''
, @TSHP07010I_01_DlvStdDays
, @TSHP07010I_01_DlvDate
, @TSHP07010I_01_DlvTime
, @TSHP07010I_01_CnstInstallTraderCd
, @TSHP07010I_01_CnstInstallSortCd
, @TSHP07010I_01_CnstInstallDlvDate
, @TSHP07010I_01_CnstInstallDlvTime
, @UserCd
, GETDATE()
, @UserCd
, GETDATE()
, GETDATE()
, '0'
, ''
, @UpdateJobMod
, '0'
)
-- Accumulate the number of inserted record
IF ( @@ROWCOUNT <> 0 )
BEGIN
SET @w_Ins_MDlvCorpCnt = @w_Ins_MDlvCorpCnt + 1
END
ELSE -- If there is no record inserted/updated at all then raise error
BEGIN
SET @w_MessageList = 'MDlvCorp'
+ CHAR ( 9 )
+ 'InputNo='
+ ISNULL ( CAST ( @TSHP07010I_01_InputSeq AS VARCHAR ( 10 ) ) , '<NULL>' )
EXECUTE FW_STP_GetApMessage @ct_MSG_MSG002, @w_MessageList, @w_Message OUTPUT
RAISERROR ( @w_Message, @ct_NONFATALEXCEPTION, @ct_RAISERRORSTATE )
END
END -- End insert
-- Set a debug trace
SET @w_Message = 'FETCH NEXT ROW FROM POSITION ['+ CAST ( @@CURSOR_ROWS AS VARCHAR ( 10 ) ) + '] OF THE CURSOR c_TSHP07010I'
-- Fetch next record
FETCH NEXT FROM c_TSHP07010I INTO
@TSHP07010I_01_InputSeq
, @TSHP07010I_01_ZipNo
, @TSHP07010I_01_TraderCd
, @TSHP07010I_01_SortCd
, @TSHP07010I_01_DlvStdDays
, @TSHP07010I_01_DlvDate
, @TSHP07010I_01_DlvTime
, @TSHP07010I_01_CnstInstallTraderCd
, @TSHP07010I_01_CnstInstallSortCd
, @TSHP07010I_01_CnstInstallDlvDate
, @TSHP07010I_01_CnstInstallDlvTime
-- Clear debug trace
SET @w_Message = ''
END
-- Set a debug trace
SET @w_Message = 'TRUNCATE TABLE TSHP07010I_01'
-- When all has been done and there is no error we will delete all the data from the table TSHP07010I_01
TRUNCATE TABLE TSHP07010I_01
-- Clear debug trace
SET @w_Message = ''
IF ( @@TRANCOUNT <> 0 )
BEGIN
--==============================================================================
--== Committed
--==============================================================================
COMMIT TRAN
SET @w_Ins_MDlvCorpCommitCnt = @w_Ins_MDlvCorpCnt
SET @w_Upd_MDlvCorpCommitCnt = @w_Upd_MDlvCorpCnt
END
END TRY
BEGIN CATCH
--==============================================================================
--== Error handling
--==============================================================================
IF ( @@TRANCOUNT <> 0 )
BEGIN
ROLLBACK TRAN
END
-- Write error log
EXECUTE FW_STP_WriteErrorLog @w_Message, @UpdateJobMod, @ct_STP_ID, @UserCd, @ErrCode OUTPUT, @ErrMsg OUTPUT
-- The error notice is done by the processing of parents.
SET @Success = @ct_SUCCESS_ERROR
END CATCH
--==============================================================================
--== Finally
--==============================================================================
BEGIN
IF ( @w_c_TSHP07010IOpenFlg = @ct_OnFlg )
BEGIN
-- Release the cursor
EXECUTE FW_STP_DisposeCursor NULL, 'c_TSHP07010I', @UpdateJobMod, @UserCd, @Success OUTPUT
END
--==============================================================================
--== Number of record regist
--==============================================================================
SET @w_MessageList = @ct_MDLVCORP_NAME + CHAR ( 9 ) + CONVERT ( VARCHAR(10), @w_Ins_MDlvCorpCommitCnt )
EXECUTE FW_STP_WriteLog @ct_MESSAGEKBN_INS, @ct_MSG_SYSERR41, @w_MessageList, @UpdateJobMod, @ct_STP_ID, @UserCd
--==============================================================================
--== Number of record update
--==============================================================================
SET @w_MessageList = @ct_MDLVCORP_NAME + CHAR ( 9 ) + CONVERT ( VARCHAR(10), @w_Upd_MDlvCorpCommitCnt )
EXECUTE FW_STP_WriteLog @ct_MESSAGEKBN_UPD, @ct_MSG_SYSERR42, @w_MessageList, @UpdateJobMod, @ct_STP_ID, @UserCd
-- End process
SET @w_MessageList = ''
EXECUTE FW_STP_WriteLog @ct_MESSAGEKBN_EOT, @ct_MSG_SYSEOT, @w_MessageList, @UpdateJobMod, @ct_STP_ID, @UserCd
END
END
GO |
Could you please investigate and give an update patch when needed (we've bought a SQL Refactor $365 licence). |
|
