Slight bug with the 'Find Unused Variables and Parameters'

Ozzie

In the code below, if you run 'Find Unused Variables and Parameters' it will errantly flag the declaration of @AuditTable as 'The variable declared here is used before it has a value assigned to it' - repro below

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
--#region Proc Header
--------------------------------------------------------------------------------------
-- Name			: usp_TestForRedGate
-- Description	: This is a test for unused variables
-- Example		: EXECUTE dbo.usp_TestForRedGate
-- Notes		: 
-- Called By	: 
--
-- Revision		Author	JIRA	Description
-- 2015-03-11	Ozzie			Changed code to work with SQL 2012 and the Sentinel migration.
--------------------------------------------------------------------------------------
--#endregion
ALTER PROCEDURE [dbo].[usp_TestForRedGate]
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE						-- User Friendly Constants
		@Succeeded				INT				= 0,
		@StoredProcedure		SYSNAME			= ISNULL( OBJECT_NAME( @@PROCID ), 'Inline Code' );

	DECLARE
		@ExecutionStatus		INT				= @Succeeded;

	DECLARE @Results TABLE
	(
		[MsgID]					INT				IDENTITY(1,1)							NOT NULL PRIMARY KEY CLUSTERED,
		[MsgData]				NVARCHAR(4000)	COLLATE SQL_Latin1_General_CP1_CI_AS	NOT NULL,
		[MsgDate]				DATETIME												NOT NULL DEFAULT( CURRENT_TIMESTAMP ),
		[Duration(ms)]			INT														NULL
	);

	DECLARE
		@AuditTable TABLE -- Flagged as 'The variable declared here is used before it has a value assigned to it'
		(
			[Action]				VARCHAR(6)		COLLATE SQL_Latin1_General_CP1_CI_AS	NOT NULL
		);

	BEGIN TRY
		INSERT INTO @Results( MsgData )
		SELECT 'Entered ' + @StoredProcedure + '.';

		MERGE dbo.InboundCorrespondenceStatus AS ICS
		USING
		(
			SELECT ICD.FileDetailID,  CURRENT_TIMESTAMP AS StatusDate
			FROM dbo.InboundCorrespondenceDetails AS ICD
			INNER JOIN dbo.tmpWelcomeLetter AS WL ON ICD.CustomerID = WL.CustomerID AND ICD.FileID = WL.InboundCorrespondenceFileID AND ICD.CorrespondenceTypeID = WL.CorrespondenceTypeID
		) AS QR ON ICS.FileDetailID = QR.FileDetailID
		WHEN MATCHED THEN
			UPDATE SET StatusDate = QR.StatusDate
		WHEN NOT MATCHED THEN
			INSERT (FileDetailID, StatusDate)
			VALUES (QR.FileDetailID, QR.StatusDate)
		OUTPUT $action AS [Action]
		INTO @AuditTable;
		
	END TRY
	BEGIN CATCH
		-- Handle the Error
		IF( @@TRANCOUNT > 0 )
		BEGIN
			ROLLBACK TRANSACTION;
		END;

		EXECUTE dbo.usp_LogErrorInfo;
	END CATCH

	RETURN @ExecutionStatus;
END;

David Priddle

Hi Ozzie,

Thanks for reporting this!

We've found out the cause and should have fixed the issue in the latest version of the SQL Prompt 6.5 beta.

Please let us know if you have any more issues.

Best regards,

David

Ozzie

Wow - 1 day turnaround - not too shabby!

Ozzie