Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

Slight bug with the 'Find Unused Variables and Parameters'

OzzieOzzie Posts: 46 Bronze 5
edited March 12, 2015 3:31PM in SQL Prompt
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;

Comments

Sign In or Register to comment.