Options

Find unused variables does not work properly with TRY~CATCH

nghinvnghinv Posts: 6
edited November 20, 2009 1:40AM in SQL Refactor Previous Versions
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: 
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).
· Share on Twitter

Comments

  • Options
    Chris AucklandChris Auckland Posts: 769
    Thanks for your post.

    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? "
    Chris
    · Share on Twitter
  • Options
    nghinvnghinv Posts: 6
    thank you Chris,

    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
    · Share on Twitter
Sign In or Register to comment.