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;