February 4, 2009 at 10:10 pm
Hi,
My task is to calculate the previous number of days a patient has stayed in a mental health facility ( I work for a Hospital).
I think I have come up with a solution however I receive the error:
Server: Msg 217, Level 16, State 1, Line 2
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Is there any way to "stop" the recursion when the limit is reached rather then showing an error?
I will also welcome advice on how perform this type of task correctly.
Please see attached .txt file containing the queries.
PS. The queries do need some work.....
PPS. The solution must work in both 2000 and 2005.
Thanks you very much for taking the time to assist me!
February 5, 2009 at 2:52 am
I don't think you need a recursive function for this.
Please post your table structure and some proper sample data.
Also post your expected result.
N 56°04'39.16"
E 12°55'05.25"
February 5, 2009 at 7:15 am
Peso (2/5/2009)
I don't think you need a recursive function for this.Please post your table structure and some proper sample data.
Also post your expected result.
Seconded
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 5, 2009 at 3:23 pm
Hi,
Expected Result
--------------------
SB_EPISODE_NUMBER, PREVIOUS_DAYS
12345, null
12346, 17
12390, null
12345, 5
Not all current psych admissions will have any prior psych admission\stay
The previous admission may or may not be psych stay
Only stays within the previous 7 days ( and 7 days prior to the last and so on backwards through time) are counted.
Please see attached schema and data example.
Thank you for your time.
February 5, 2009 at 4:58 pm
hi,
I managed to re-write my function to use a while-loop istead of recursion.
ALTER FUNCTION GET_PREVIOUS_MENTAL_HEALTH_DAYS
(
@EPISODE_NUMBER NUMERIC
)
RETURNS INT
AS
BEGIN
DECLARE @TOTAL_PREVIOUS_DAYS AS INT
DECLARE @PREV_DAYS AS INT
SET @TOTAL_PREVIOUS_DAYS = 0
WHILE @TOTAL_PREVIOUS_DAYS <= 60 AND @EPISODE_NUMBER IS NOT NULL
BEGIN
SET @PREV_DAYS = 0
SELECT
@PREV_DAYS = SUM( DATEDIFF(D, STAY.SB_WARD_STAY_START, STAY.SB_WARD_STAY_END))
FROM
dbo.SB_WARD_STAY STAY INNER JOIN
dbo.SB_WARD WARD ON
STAY.SB_WARD_ID = WARD.SB_WARD_ID AND
WARD.SB_PSYCHIATRIC_WARD = 'Y'
WHERE
STAY.SB_EPISODE_NUMBER = @EPISODE_NUMBER
SET @TOTAL_PREVIOUS_DAYS = @TOTAL_PREVIOUS_DAYS + @PREV_DAYS
SET @EPISODE_NUMBER = DBO.GET_PREVIOUS_MENTAL_HEALTH_STAY( @EPISODE_NUMBER )
END
IF @TOTAL_PREVIOUS_DAYS > 60
SET @TOTAL_PREVIOUS_DAYS = 60
RETURN @TOTAL_PREVIOUS_DAYS
END
February 6, 2009 at 12:09 am
If it works for you, great.
But you should consider rewriting the code as set-based instead.
N 56°04'39.16"
E 12°55'05.25"
February 6, 2009 at 8:45 am
Hi
First of all you dont need a recursive function to achive this.
Try achieveing it using loops. If you still want to go ahead with your current logic then you can use @@NESTLEVEL to see what is the recursion level of the function.
Thanks
Vijaya Kadiyala
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply