Find unused variables does not work properly with TRY~CATCH
nghinv
Posts: 6
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:
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:
Could you please investigate and give an update patch when needed (we've bought a SQL Refactor $365 licence).
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:
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:
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).
Comments
I see what you mean. SQL Refactor doesn't consider every possible point of failure within a try/catch block, so it thinks that the variable is just simply reassigned before it is used.
I have logged this in our developenet database for consideration in a future version. For your reference the tracking code for this is SR-861.
With regards to your other query, you actually already have an open support ticket for this. F0028721.
I responded on 16th November with
"I've had a look through the sp, and I didn't see any variables highlighted that shouldn't be.
Most of the warnings seem to be for 'Value is assigned but never used'. This just means that a variable is assigned a value, but before that value is used, the variable is assigned a different value.
The first case of this is '@ct_OffFlg INT = 0', which is never used. '@w_c_TSHP07010IOpenFlg' is set to '@ct_OffFlg' when it is declared, but it gets set to '@ct_OnFlg' before it is used. Therefore SQL Refactor is telling you that '@ct_OffFlg' is never used.
I read through most of the tooltip messages for the underlined variables, and they seem to be correct.
Do you have any specific examples of an underlined variable that shouldn't be? "
And I'm sorry for missing mails. I've just read the emails and replied.
I see that your answer here (SR-861) is correct.
thank you for your support,
Nghi