Function is Causing Deadlocks

[Vikki McCormick]

Hi

We have a function that is causing deadlocks. I can't see anything wrong with it. Can anyone weigh in?


FUNCTION that is Called in Stored Procedures to find Previous Business Date of a specific date.

i.e. Select [dbo].[GetPreviousBusi nessDate] ('05/13/12')

CREATE FUNCTION [dbo].[GetPreviousBusinessDate] 
(
	@inDate date
)
RETURNS date
AS
BEGIN
	DECLARE @Result date
	DECLARE @nextDate date
	SET @nextDate = @inDate
	
	WHILE(dbo.IsHoliday(@nextDate)=1 OR dbo.IsWeekEndDay(@nextDate)=1)
	BEGIN
		SET @nextDate = DATEADD("D",-1,@nextDate)
	END
	
	SELECT @Result = @nextDate
	
	
	RETURN @Result

END




CREATE FUNCTION [dbo].[IsHoliday] 
(
	@inDate date
)
RETURNS bit
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result bit

	-- Add the T-SQL statements to compute the return value here
	SELECT @Result = COUNT(HolidayName) FROM FederalHolidaysTable WHERE HolidayDate = @inDate

	-- Return the result of the function
	RETURN @Result

END




CREATE FUNCTION [dbo].[IsWeekEndDay]
(
	@inDate date
)
RETURNS bit
AS
BEGIN
	
	DECLARE @Result bit
	SET @Result = 0
	
	IF DATENAME("DW",@inDate) IN ('Saturday','Sunday')
		SET @Result = 1
	
	RETURN @Result

END

[ck9663]

Check your IsHoliday function. Use exists instead of count. Do you have an index on that table?


~~ CK

[Vikki McCormick]

CK Thanks. Ok changed to exists and we already have an index on that table, but I will check to see if any adjustments should be made.

So far so good, it's holding steady. Still getting some very minor blocking and they are resolving very quickly.

Cool. Thanks again.

[Rabbit]

Seeing as how you're not modifying any data, have you tried the NOLOCK hint?

SELECT *
FROM someTable NOLOCK