ALTER PROCEDURE [dbo].[TEST]
(
@AcctgPeriod int = NULL,
@ReturnValue As int OUTPUT,
@RecordCount As int OUTPUT,
@ErrMsg AS nVarChar(MAX) OUTPUT
)
AS
BEGIN
USE [DataWarehouse]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TEST]
(
@ReturnValue As int OUTPUT,
@RecordCount As int OUTPUT,
@ErrMsg AS nVarChar(MAX) OUTPUT,
@AcctgPeriod int = NULL
--@AcctgPeriod int = -1 --works fine!
--@AcctgPeriod int
)
AS
BEGIN
SET @ReturnValue = 0
SET @ErrMsg = 'No Errors'
IF (@ReturnValue = 0)
BEGIN TRY
--DECLARE @AcctgPeriod int = NULL; when removed from the parameter list, the stored proc executes fine.
--IF COALESCE(@AcctgPeriod, 0) = 0 -- hangs or takes forever
--IF @AcctgPeriod = NULL -- evaluates to false and doesn't execute the begin/end
--IF @AcctgPeriod = -1 -- works fine
--IF (1=1)
IF (@AcctgPeriod IS NULL) -- hangs or takes forever
BEGIN
SELECT @AcctgPeriod = CAST(MAX(AcctgPeriod) AS int)
FROM ArchRe.dbo.Trandetl;
SET @AcctgPeriod = CASE
WHEN CAST(RIGHT(@AcctgPeriod,2) AS smallint) IN (1,2,3) THEN CAST((CAST(LEFT(@AcctgPeriod,4) AS nvarchar(4)) + '03') AS int)
WHEN CAST(RIGHT(@AcctgPeriod,2) AS smallint) IN (4,5,6) THEN CAST((CAST(LEFT(@AcctgPeriod,4) AS nvarchar(4)) + '06') AS int)
WHEN CAST(RIGHT(@AcctgPeriod,2) AS smallint) IN (7,8,9) THEN CAST((CAST(LEFT(@AcctgPeriod,4) AS nvarchar(4)) + '09') AS int)
ELSE CAST((CAST(LEFT(@AcctgPeriod,4) AS nvarchar(4)) + '12') AS int)
END;
END