Optional Input Parameter in Stored Proc kills performance

Hey All -

I have the stored procedure whose performance dies when I use an input parmeter set to NULL:

ALTER PROCEDURE [dbo].[TEST]
   (
	@AcctgPeriod int = NULL,
         @ReturnValue As int OUTPUT,
	@RecordCount As int OUTPUT,
	@ErrMsg AS nVarChar(MAX) OUTPUT
	)
AS
BEGIN

Open in new window


When "@AcctgPeriod int = NULL" is changed to "@AcctgPeriod int = -1" then the thing runs great.

Can't find anything to explain this.

Other informaiton ... using SQL Server 2008 r2.
MarkMahonAsked:
Who is Participating?
 
dbaSQLCommented:
Kind of hard to say without seeing the rest of the procedure.  Only one thing I can ask, are you setting ANSI NULLS OFF at procedure creation?  
0
 
MarkMahonAuthor Commented:
Here is some more code.  I'll just say that I can change the input parameter's value from "Null" to "-1" and the thing works in seconds ... that's why I gave the limited code sample.  

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

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
MarkMahonAuthor Commented:
Thanks dbaSQL, I'll give it a try.

Mark
0
 
MarkMahonAuthor Commented:
dbaSQL -

Gave it a try and it works.  Thanks for the solution.

I'm also thinking of using
@AcctgPeriod int = -1 (and not @AcctgPeriod int = Null)
so that I have minimal changes in the stored proc (i.e., I won't have to change all of the @AcctgPeriod references to AcctgPeriod_i)

Butif "=Null" makes it optional but "= -1" I makes it required then I'll have to go with the solution in your post.

Thanks again, Mark
0
 
dbaSQLCommented:
Excellent!  Glad it worked out.
0
 
MarkMahonAuthor Commented:
It is interesting that the issue has been around since the 2000 version, but does not appear to be addressed by 2005 or 2008.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.